求助:SQL语句统计表的信息

发布网友

我来回答

共3个回答

热心网友

不知道你什么数据库
如果是 Oracle 的话.

SELECT
brand_id ,
count(color),
count(size) ,
WMSYS.WM_CONCAT(color) as color,
WMSYS.WM_CONCAT(size) as size
FROM
proct
GROUP BY
brand_id

热心网友

SELECT*
FROM(
SELECT DISTINCT
brand_id
FROM proct
)A
OUTER APPLY(
select
COUNTcolor=(
SELECT count(color) as si FROM proct I
WHERE brand_id = A.brand_id)
) I
OUTER APPLY(
select
COUNTsize=(
SELECT count(size) as si FROM proct P
WHERE brand_id = A.brand_id)
) P
OUTER APPLY(
SELECT
color=STUFF(REPLACE(REPLACE(
(
SELECT color FROM proct N
WHERE brand_id = A.brand_id
FOR XML AUTO
), '<N color="', ','), '"/>', ''), 1, 1, '')

) N
OUTER APPLY(
SELECT
size=STUFF(REPLACE(REPLACE(
(
SELECT size FROM proct M
WHERE brand_id = A.brand_id
FOR XML AUTO
), '<M size="', ','), '"/>', ''), 1, 1, '')

) M

热心网友

求SQL语句：统计各个brand的color及size一个有多少

这个

select
brand_id ,sum(color) , sum(size) from proct group by brand_id