mysql查询显示组数据

lh80um4z 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(242)

我有下面这个sql
数据

CREATE TABLE data
    (id int, name varchar(9), room varchar(9), paid varchar(9));

INSERT INTO data
    (id, name, room, paid)
VALUES
    (1, 'jane', 'room1','0'),
    (2, 'any', 'room2', '1'),
    (3, 'mandy', 'room2', '1'),
    (4, 'vivian', 'room2', '1'),
    (5, 'amber', 'room3', '1'),
    (6, 'fany', 'room4', '1'),
    (7, 'diana', 'room4', '0');

查询

SELECT 
  data.room, COUNT(data.id) as total
FROM 
  data
WHERE 
  data.paid = 0
GROUP BY
  data.room

示例链接sqlfiddle
现在的结果是

room 1 = 1 
room 4 = 1

我想要的结果

room 1 = 1
room 2 = 0
room 3 = 0
room 4 = 1

mysql

来源：https://stackoverflow.com/questions/50639050/mysql-query-display-group-data

4条答案

按热度按时间

8ljdwjyq1#

cdaiga的答案是正确的，但取决于你的价值观（拥有 0 以及 1 允许您执行 SUM 技巧）。通常，您希望将原始表与源表的表左联接 WHERE 以及 GROUP BY .

SELECT DISTINCT original.room, IFNULL(pr.total, 0) AS total
FROM data original LEFT OUTER JOIN (
    SELECT 
      data.room as paidrooms, COUNT(data.id) as total
    FROM 
      data
    WHERE
      data.paid = 0
    GROUP BY
      data.room
) as pr on original.room = pr.paidrooms

链接到sqlfiddle

赞(0）回复(0）举报 2021-06-20

nxagd54h2#

SELECT 
  data.room, SUM(data.paid = 0) as total
FROM 
  data
GROUP BY
  data.room;

查看SQLFiddle的演示。

赞(0）回复(0）举报 2021-06-20

vlju58qv3#

据我所知，您要列出所有房间的未付帐单总额。可以使用嵌套的select，如下所示：

SELECT 
    d1.room, 
    (SELECT COUNT(d2.room) FROM data d2 WHERE d1.room = d2.room and d2.paid = 0)   as total
    FROM 
        data d1
    GROUP BY
        d1.room

在这里摆弄

赞(0）回复(0）举报 2021-06-20

pqwbnv8z4#

SELECT 
  data.room, SUM(CASE WHEN data.paid = '0' THEN 1 ELSE 0 END) as total
FROM 
  data
GROUP BY
  data.room

请参见sqlfiddle

赞(0）回复(0）举报 2021-06-20

我来回答

mysql查询显示组数据

4条答案

相关问题

热门标签

最新问答