怎么写这样的ACCESS语句？

chengzhiting

access中能做出type列的结果吗？
201307，201308，201309中任何2个月的sales〉1180，那201307的type=1
201308，201309，201310中任何2个月的sales〉1180，因为现在只有1个月的sales>1180,所以201308的type=0

wuxiang_123

{:soso_e100:}能，请上传文件。

chengzhiting

wuxiang_123 发表于 2013-11-6 14:49
能，请上传文件。

以上传附件，谢谢

amazeyeli

[code=sql]USE tempdb
GO
IF OBJECT_ID('TEST') IS NOT NULL
  DROP TABLE TEST;
GO
CREATE TABLE TEST
(
  months    DATE,
  customer  NVARCHAR(8),
  sales     INT
);
GO
INSERT INTO TEST VALUES('2013-7-1','ABC',1180);
INSERT INTO TEST VALUES('2013-8-1','ABC',0);
INSERT INTO TEST VALUES('2013-9-1','ABC',1180);
INSERT INTO TEST VALUES('2013-10-1','ABC',0);
INSERT INTO TEST VALUES('2013-11-1','ABC',1180);
INSERT INTO TEST VALUES('2013-12-1','ABC',0);
GO
----------------------------------------
SELECT A.*,CASE WHEN A.sales+B.sales+C.sales>=1180*2 THEN 1 ELSE 0 END AS type
FROM TEST AS A
LEFT JOIN TEST AS B
ON MONTH(A.months)=MONTH(B.months)-1
LEFT JOIN TEST AS C
ON MONTH(A.months)=MONTH(C.months)-2
----------------------------------------
/*
months     customer sales       type
---------- -------- ----------- -----------
2013-07-01 ABC      1180        1
2013-08-01 ABC      0           0
2013-09-01 ABC      1180        1
2013-10-01 ABC      0           0
2013-11-01 ABC      1180        0
2013-12-01 ABC      0           0

(6 行受影响)
*/
----------------------------------------
--请换成access支持的语法,例如:case when 换成 iif[/code]

wuxiang_123

1. SELECT a.MONTH,a.customer,iif(sum(A.sales>=1180)<=-2,1,0) AS TYPE
   
2. FROM Sheet1 AS a
   
3. left join
   
4. SHEET1 AS B
   
5. on A.customer=B.customer and (A.month Between b.month-2 and b.month)
   
6. group by a.MONTH,a.customer

复制代码

chengzhiting

wuxiang_123 发表于 2013-11-6 15:55

iif(sum(A.sales>=1180)<=-2,1,0)
这个是什么意思呀，看不明白

amazeyeli

A.sales>=1180 如为ture,结果则为-1,如为f,则为0
sum(A.sales>=1180)<=-2 则至少有两个为1180

chengzhiting

amazeyeli 发表于 2013-11-6 17:04
A.sales>=1180 如为ture,结果则为-1,如为f,则为0
sum(A.sales>=1180)

谢谢老师的解释

chengzhiting

wuxiang_123 发表于 2013-11-6 15:55

附件中查询2运行报错，请老师帮我看下是哪个的数据类型不对，谢谢

amazeyeli

有null值
改为 iif(sum(a.sales)>=1180*2,1,0) AS status