SELECT Tabname, Colname
FROM Systemcatalog
WHERE Tabschema = 'X'
给我
Tabname, Colname
A X1
A Y1
A Z1
B X2
C X3
C Y2
C Z2
...
但我需要的是:
Tabname, Colnum
A 3
B 1
C 3
...
因此,计算每个表的所有列,将其放入新列中,同时删除 TabName 中的双元素。
您可以使用
group by
和count
。
SELECT Tabname
, count(*) as Colnum
FROM Systemcatalog
GROUP BY Tabname
WHERE Tabschema = 'X'
使用 group by
获取聚合计数。
SELECT Tabname, count(Colname) as total_columns
FROM Systemcatalog
WHERE Tabschema = 'X'
GROUP BY Tabname