fund表
ID accountid 支出资金 收入资金
1 10 99.1 null
2 11 99.1 null
3 12 null 80fund表
accountid accountnum
10 中国银行
11 人民银行
12 现金
... ...显示的效果
ID accountid 支出资金 收入资金 中国银行 人民银行 现金 ......
1 10 99.1 null 1 0 0 .....
2 11 99.1 null 0 1 0 ......
3 12 null 80 0 0 1 ......
.... 有2个表,一个是 account, fund, accountid是关联的/
我要显示的效果是 accountnum 字段横这排。
现金,中国银行,其他 等等。 下面的 0和1 的意思就是 fund.accountid =account.accountid 就显示问1, 不等于就显示为 0。
select cy_item.cy_size as 项目规格,cy_item.cy_tsyq as 特殊要求,cy_item.cy_num as 冲印数量, isnull(cy_h_num,0) as 回单数量, isnull(cy_f_num,0) as 返工数量,isnull(cy_zk_num,0) as 重开数量 from cy_item left join h_item on cy_item.cy_custdo_id=h_item.cy_custdo_id and cy_item.cy_size=h_item.cy_size and cy_item.xh=h_item.xh left join f_item on cy_item.cy_custdo_id=f_item.cy_custdo_id and cy_item.cy_size=f_item.cy_size and cy_item.xh=f_item.xh left join zk_item on cy_item.cy_custdo_id=zk_item.cy_custdo_id and cy_item.cy_size=zk_item.cy_size and cy_item.xh=zk_item.xh where cy_item.cy_custdo_id='" & GridView1.Rows(e.NewSelectedIndex).Cells(1).Text & "'"
SELECT account.*,
(SELECT COUNT(*)
FROM fund, account
WHERE fund.accountid = account.accountid) AS 中国银行,
(SELECT COUNT(*)
FROM fund, account
WHERE fund.accountid = account.accountid) AS 人民银行,
(SELECT COUNT(*)
FROM fund, account
WHERE fund.accountid = account.accountid) AS 现金
FROM fund account
select
account.id, account.accountid, account.支出资金, account.收入资金,
(case accountnum when '中国银行' then 1 else 0 end) as 中国银行,
(case accountnum when '人民银行' then 1 else 0 end) as 人民银行,
(case accountnum when '现金' then 1 else 0 end) as 现金
from
account inner join fund
on
account.accountid = fund.accountid
1 10 99.1000 NULL 1 0 0
2 11 99.1000 NULL 0 1 0
3 12 NULL 80.0000 0 0 1