求解一条SQL，列做与运算

[zjh527]

checkno xzqh downstatus upstatus
200803 370101 0 0
200803 370102 1 0
200803 360101 0 1
200803 360102 0 0
200803 350101 1 0
200803 350102 1 0
按xzqh前２位分组， 同组的downstatus和upstatus分别作与运算
结果：
200803 370000 0 0
200803 360000 0 0
200803 350000 1 0

[中国风]

select heckno  ,left(xzqh,2)+'0000' as xzqh,sum(downstatus) as downstatus,  sum(upstatus) as upstatus from table1 group by heckno  ,left(xzqh,2)

[Flashield]

楼上的，人家要的是逻辑与操作哦。

--一个讨巧的方法
select checkno ,substr(xzqh,1,2)+'0000' as xzqh,min(downstatus) as downstatus, min(upstatus) as upstatus
from table1
group by checkno ,substr(xzqh,1,2)

[中国风]

select heckno ,substr(xzqh,1,2)||'0000' as xzqh,--oracle這里要改改
sum(downstatus) as downstatus, sum(upstatus) as upstatus from table1 group by heckno ,substr(xzqh,1,2)

[中国风]

select 
	heckno ,
	substr(xzqh,1,2)||'0000' as xzqh,
	min(downstatus) as downstatus,  --取最小值
	min(upstatus) as upstatus  --取最小值
from 
	table1 
group by heckno ,substr(xzqh,1,2)

[zjh527]

[Quote=引用 1 楼 roy_88 的回复:]
SQL codeselect heckno ,left(xzqh,2)+'0000' as xzqh,sum(downstatus) as downstatus, sum(upstatus) as upstatus from table1 group by heckno ,left(xzqh,2)
[/Quote]


呵呵，想到一起去了，已经用这个方法解决了。

[zjh527]

[Quote=引用 2 楼 Flashield 的回复:]
楼上的，人家要的是逻辑与操作哦。

--一个讨巧的方法
select checkno ,substr(xzqh,1,2)+'0000' as xzqh,min(downstatus) as downstatus, min(upstatus) as upstatus
from table1
group by checkno ,substr(xzqh,1,2)
[/Quote]


晕~引用错回帖了，因该是这个引用。