字符串的小计和总计 [英] subtotal and total for strings

问题描述

我有一个名为 abc 的表，其中包含 2 列城市和房屋.(我使用的是 MYSQL 6.0)

I have a table named abc with 2 columns-city and houses.(I am using MYSQL 6.0)

 houses            city
    A               P
    B               Q
    C               P
    D               R
    E               P
    F               S
    G               S

我想要这样的输出:

houses             city
A                   p                  
C                   p
E                   p
noofhouses:         3

B                   Q
noofhouses:         1 

D                   R
noofhouses:         1

F                   S
G                   S
noofhouses:         2
totalhouses         7

所以基本上我想要一个房子的名字和没有的列表.在一个城市的房子.那可能吗??请帮忙.提前致谢.

So basically i want a list of names of houses and no. of houses in a city. is that possible?? please help. Thanks in advance.

推荐答案

如果您确实想完全在 SQL 中执行此操作，可以使用:

If you did want to do this entirely in SQL, you could use:

SELECT IFNULL(Title, aHouse) AS House, IFNULL(Total, aCity) AS City
FROM
(
    SELECT House AS aHouse, City AS aCity, NULL AS Total, NULL AS Title
    FROM @abc 
    UNION 
    SELECT NULL AS aHouse, City AS aCity, CAST(COUNT(House)  AS CHAR(4)) AS Total, 'noofhouses:' AS Title
    FROM @abc
    GROUP BY City
    UNION 
    SELECT NULL AS aHouse, 'zzzzz' AS aCity, CAST(COUNT(House)  AS CHAR(4)) AS Total, 'total:' AS Title
    FROM @abc
) a
ORDER BY aCity, Title, aHouse 

输出:

House       City
----------- ----
A           P   
C           P   
E           P   
noofhouses: 3   
B           Q   
noofhouses: 1   
D           R   
noofhouses: 1   
F           S   
G           S   
noofhouses: 2   
total:      7   

SQLFiddle

这篇关于字符串的小计和总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！