表结构是
其中istitle＝0代表它是标题
其余值是属于该标题的id
我想得到这样的数据
id bid bname bnum istitle
21 1 请问您是： 6 0
24 1 请问您的年龄是： 4 0
------解决方案--------------------
select a.id,a.bid,a.bname,sum(b.bnum)[bnum],a.istitle from
( select *from t where istitle=0)a left join
( select *from t where istitle!=0)b on a.id=b.istitle group by
a.id,a.bid,a.bname,a.istitle
第二行的记录bnum 应该是5吧。
------解决方案--------------------
WITH test (id,bid,type,bname,bnum,istitle) AS (
SELECT 21,1,0,'请问你是：' ,0,0 UNION ALL
SELECT 22,1,0,'先生' ,3,21 UNION ALL
SELECT 23,1,0,'女士' ,3,21 UNION ALL
SELECT 24,1,0,'请问你的年龄是：',0, 0 UNION ALL
SELECT 25,1,0,'18岁以下' ,1,24 UNION ALL
SELECT 26,1,0,'18-24岁' ,3,24 UNION ALL
SELECT 27,1,0,'24-30岁' ,1,24
),
answer AS (
SELECT istitle,
SUM(bnum) bnum
FROM test
WHERE istitle <> 0
GROUP BY istitle
)
SELECT t.id,
t.bid,
t.bname,
a.bnum,
0 istitle
FROM test t
JOIN answer a
ON a.istitle = t.id
id bid bname bnum istitle
----------- ----------- ---------------- ----------- -----------
21 1 请问你是： 6 0
24 1 请问你的年龄是： 5 0