Strawberry的社区主页

Strawberry 最近回复了

6 年前

回复了 Strawberry 创建的主题 » 如果列名包含且值等于mysql,则选择count

规范化的模式可能如下所示

users
user_id name
1       Clem
2       Kevin

training
user_id date       sport 
      1 2018-11-15 Handball
      1 2018-11-11 Football

5 年前

回复了 Strawberry 创建的主题 » 在mysql中基于用户id获取购买号

仅适用于8.0之前的版本…

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(purchase_id SERIAL PRIMARY KEY
,user CHAR(1) NOT NULL
,date DATE NOT NULL
);

INSERT INTO my_table VALUES
(1,'a','2018-01-01'),
(2,'b','2018-01-02'),
(3,'a','2018-01-02'),
(4,'a','2018-01-03'),
(5,'b','2018-01-04'),
(6,'a','2018-01-04');    

SELECT a.purchase_id
     , a.user
     , a.date
     , a.i rank
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = user THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev := user
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY user
            , date
     ) a
 ORDER   
    BY purchase_id;
+-------------+------+------------+------+
| purchase_id | user | date       | rank |
+-------------+------+------------+------+
|           1 | a    | 2018-01-01 |    1 |
|           2 | b    | 2018-01-02 |    1 |
|           3 | a    | 2018-01-02 |    2 |
|           4 | a    | 2018-01-03 |    3 |
|           5 | b    | 2018-01-04 |    2 |
|           6 | a    | 2018-01-04 |    4 |
+-------------+------+------------+------+

6 年前

回复了 Strawberry 创建的主题 » mysql按排序列值选择多个列组

考虑一下下面的一个标准化数据集…

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL
,n INT NOT NULL
,val INT NOT NULL
,PRIMARY KEY(id,n)
);

INSERT INTO my_table VALUES
(1, 1, 3),
(1, 2, 2),
(1, 3, 1),
(2, 1, 6),
(2, 2, 5),
(2, 3, 7),
(3, 1, 2),
(3, 2, 3),
(3, 3, 1),
(4, 1, 1),
(4, 2, 6),
(4, 3, 5),
(5, 1, 5),
(5, 2, 6),
(5, 3, 7),
(6, 1, 3),
(6, 2, 5),
(6, 3, 6);

这里有一个快速(写)和肮脏的解决方案。提供更快/更优雅的解决方案…

SELECT vals
     , COUNT(*) total
  FROM 
     ( SELECT id
            , GROUP_CONCAT(val ORDER BY val) vals 
         FROM my_table 
        GROUP 
           BY id
     ) x 
 GROUP 
    BY vals;
+-------+-------+
| vals  | total |
+-------+-------+
| 1,2,3 |     2 |
| 1,5,6 |     1 |
| 3,5,6 |     1 |
| 5,6,7 |     2 |
+-------+-------+

6 年前

回复了 Strawberry 创建的主题 » 用户24小时按一次按钮?php-mysql

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(id SERIAL PRIMARY KEY
,user_id INT NOT NULL
,dt DATETIME NOT NULL
);

INSERT INTO my_table (user_id,dt) VALUES (101,NOW());
Query OK, 1 row affected (0.00 sec)

SELECT * FROM my_table;
+----+---------+---------------------+
| id | user_id | dt                  |
+----+---------+---------------------+
|  1 |     101 | 2018-10-07 23:21:44 |
+----+---------+---------------------+
1 row in set (0.00 sec)

……大约一分钟后……

INSERT INTO my_table (user_id,dt) 
SELECT 101,NOW() 
  FROM (SELECT 1) x 
  LEFT 
  JOIN my_table y 
    ON y.user_id = 101 
   AND y.dt >= NOW() - INTERVAL 60 SECOND 
 WHERE y.id IS NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM my_table;
+----+---------+---------------------+
| id | user_id | dt                  |
+----+---------+---------------------+
|  1 |     101 | 2018-10-07 23:21:44 |
|  2 |     101 | 2018-10-07 23:23:39 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

……几秒钟后……

INSERT INTO my_table (user_id,dt) 
SELECT 101,NOW() 
  FROM (SELECT 1) x 
  LEFT 
  JOIN my_table y 
    ON y.user_id = 101 
   AND y.dt >= NOW() - INTERVAL 60 SECOND 
 WHERE y.id IS NULL;
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM my_table;
+----+---------+---------------------+
| id | user_id | dt                  |
+----+---------+---------------------+
|  1 |     101 | 2018-10-07 23:21:44 |
|  2 |     101 | 2018-10-07 23:23:39 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

6 年前

回复了 Strawberry 创建的主题 » 使用PHP从MySQL数据库中的下拉列表显示图像

没有答案;评论时间太长:

这是胡说八道,使其他人无法理解:

SELECT * 
  FROM `produse` 
 WHERE 1`id_produs`
     , `numep`
     , `standard`
     , `fisa_tehnica`
     , `pret`

6 年前

回复了 Strawberry 创建的主题 » 只获取最新的时间戳条目mysql

SELECT a.* 
  FROM 
     ( SELECT *
            , o.methodName oldName
            , n.methodName newName
            , s.firstName fName
            , s.lastName lName 
         FROM changeReport r
         LEFT 
         JOIN methodLookup o
           ON o.methodID = r.oldMethod
         LEFT 
         JOIN methodLookup n
           ON n.methodID = r.newMethod
         JOIN s s
           ON s.sID = r.studentID
         LEFT 
         JOIN staffaccounts a
           ON r.staffID = a.staffID
        WHERE 31 IN (newSubMethodm,oldSubMethod) 
          AND DATE(timestamp) = CURRENT_DATE
     ) a
  JOIN 
     ( SELECT s.sid
            , MAX(timestamp) timestamp
         FROM changeReport r
         LEFT 
         JOIN methodLookup o
           ON o.methodID = r.oldMethod
         LEFT 
         JOIN methodLookup n
           ON n.methodID = r.newMethod
         JOIN s s
           ON s.sID = r.studentID
         LEFT 
         JOIN staffaccounts a
           ON r.staffID = a.staffID
        WHERE 31 IN (newSubMethodm,oldSubMethod) 
          AND DATE(timestamp) = CURRENT_DATE
        GROUP
           BY s.sid 
    ) b
   ON b.sid = a.sid
  AND b.timestamp = a.timestamp;

» Strawberry 创建的更多回复