表如下:
ProductID price
1 4 0
1 7 0
2 6 1
2 7 1
2 8 1
3 11 0
3 50 0
3 55 0
3 60 0
4 20 1
4 30 1说明:
当为0时,取ProductID里的最大值,
当为1时,取ProductID里的最小值。即得到的结果集为:
ProductID price
1 7 0
2 6 1
3 60 0
4 20 1最终要的结果是: 结果集里的price的总和。
在线等待答案~~~~~
谢谢~
ProductID price
1 4 0
1 7 0
2 6 1
2 7 1
2 8 1
3 11 0
3 50 0
3 55 0
3 60 0
4 20 1
4 30 1说明:
当为0时,取ProductID里的最大值,
当为1时,取ProductID里的最小值。即得到的结果集为:
ProductID price
1 7 0
2 6 1
3 60 0
4 20 1最终要的结果是: 结果集里的price的总和。
在线等待答案~~~~~
谢谢~
SELECT PRODUCTID,CASE MAX(MARK) WHEN 0 THEN MAX(PRICE) ELSE MIN(PRICE) END,MARK
FROM TB
drop table tb
Go
Create table tb([ProductID] int,[price] int,[] int)
Insert tb
select 1,4,0 union all
select 1,7,0 union all
select 2,6,1 union all
select 2,7,1 union all
select 2,8,1 union all
select 3,11,0 union all
select 3,50,0 union all
select 3,55,0 union all
select 3,60,0 union all
select 4,20,1 union all
select 4,30,1
Go
Select [ProductID],
case when []=0 then max([price])else min([price])end,
[]
from tb
group by [ProductID],[]
/*
ProductID
----------- ----------- -----------
1 7 0
3 60 0
2 6 1
4 20 1(4 個資料列受到影響)
*/
SELECT SUM(PRICE) AS PRICE
FROM(
SELECT CASE MAX(MARK) WHEN 0 THEN MAX(PRICE) ELSE MIN(PRICE) END AS PRICE
FROM TB
GROUP BY PRODUCTID,MARK
) T
if not object_id('tb') is null
drop table tb
Go
Create table tb([ProductID] int,[price] int,[] int)
Insert tb
select 1,4,0 union all
select 1,7,0 union all
select 2,6,1 union all
select 2,7,1 union all
select 2,8,1 union all
select 3,11,0 union all
select 3,50,0 union all
select 3,55,0 union all
select 3,60,0 union all
select 4,20,1 union all
select 4,30,1
SELECT SUM(PRICE) FROM (
SELECT PRODUCTID,CASE MAX(MARK) WHEN 0 THEN MAX(PRICE) ELSE MIN(PRICE) END AS PRICE,MAX(MARK) AS MARK
FROM TB
GROUP BY PRODUCTID
) T
--93
出现一个标题为“ACCESS_SQL”,内容为:Method'~' of object'~' failed 的错误啊!
ps:我用的是access数据库
是的,每组PRODUCTID中的MARK一致
那么说在access里就肯定实现不了这样的功能?