求解一个疑难的SQL语句
CREATE TABLE ORDERS
(
OrderID VARCHAR(50),
MemberID VARCHAR(100),
CREATEON DATETIME
)
INSERT INTO Orders
SELECT 'FP1','1','2012-05-12' UNION
SELECT 'FP2','1','2012-05-13' UNION
SELECT 'FP3','1','2012-05-14' UNION
SELECT 'FP4','2' ,'2012-05-12'
CREATE TABLE OrderShipping
(
OrderID VARCHAR(50) ,
Address1 VARCHAR(50),
Address2 VARCHAR(50),
CountryName VARCHAR(50),
ProviNce VARCHAR(50)
)
INSERT INTO OrderShipping
SELECT 'FP1', '金华1','金华','中国1','浙江' UNION
SELECT 'FP2', '金华1','金华','中国1','浙江' UNION
SELECT 'FP3', '金华2','金华','中国2','浙江' UNION
SELECT 'FP4', '深圳','深圳','中国2','广东'
需求实现功能:已知Orders表里面的MemberID 是传进来的参数 判断如果memberid 对应的 orderid有多条则做逻辑处理
得出来的就是 memberid =1 的 三条记录 对应OrderShipping (订单发货信息表) 三条明细 找出最后一条和倒数第二条判断地址是否有变更如果有则做处理
SQL怎么写啊,疑惑:如何根据memberid查出多条的订单号再去关联订单发货表,再拿最后一条记录去和倒数第二条做比较呀 随便一个信息不匹配的都为异常
求高手帮忙下。
结果应该是 ordershipping 表里面的 FP3 和FP2做比较 得出来结果为地址不一致为异常
[解决办法]
CREATE TABLE ORDERS( OrderID VARCHAR(50), MemberID VARCHAR(100), CREATEON DATETIME)INSERT INTO OrdersSELECT 'FP1','1','2012-05-12' UNIONSELECT 'FP2','1','2012-05-13' UNIONSELECT 'FP3','1','2012-05-14' UNIONSELECT 'FP4','2' ,'2012-05-12'CREATE TABLE OrderShipping( OrderID VARCHAR(50) , Address1 VARCHAR(50), Address2 VARCHAR(50), CountryName VARCHAR(50), ProviNce VARCHAR(50))INSERT INTO OrderShippingSELECT 'FP1', '金华1','金华','中国1','浙江' UNIONSELECT 'FP2', '金华1','金华','中国1','浙江' UNIONSELECT 'FP3', '金华2','金华','中国2','浙江' UNIONSELECT 'FP4', '深圳','深圳','中国2','广东' if OBJECT_ID('pro_test')is not nulldrop proc pro_testgocreate proc pro_test(@menberid int)asselect px=ROW_NUMBER()over(order by getdate()),t.OrderID,n.Address1,n.Address2,n.CountryName,n.ProviNce into #test from(select OrderID,MemberID,count(OrderID)over(partition by MemberID) as Totalfrom ORDERS)tinner join OrderShipping non t.OrderID=n.OrderIDwhere t.Total>=2 and t.MemberID=@menberiddeclare @px intselect @px=max(px) from #testif (select ProviNce from #test where px=@px) <>(select ProviNce from #test where px=@px-1)print '地址不一致为异常'else print '地址一致为正常'goexec pro_test 1/*地址一致为正常*/
[解决办法]
CREATE TABLE ORDERS( OrderID VARCHAR(50), MemberID VARCHAR(100), CREATEON DATETIME)INSERT INTO OrdersSELECT 'FP1','1','2012-05-12' UNIONSELECT 'FP2','1','2012-05-13' UNIONSELECT 'FP3','1','2012-05-14' UNIONSELECT 'FP4','2' ,'2012-05-12'CREATE TABLE OrderShipping( OrderID VARCHAR(50) , Address1 VARCHAR(50), Address2 VARCHAR(50), CountryName VARCHAR(50), ProviNce VARCHAR(50))INSERT INTO OrderShippingSELECT 'FP1', '金华1','金华','中国1','浙江' UNIONSELECT 'FP2', '金华1','金华','中国1','浙江' UNIONSELECT 'FP3', '金华2','金华','中国2','浙江' UNIONSELECT 'FP4', '深圳','深圳','中国2','广东' if OBJECT_ID('pro_test')is not nulldrop proc pro_testgocreate proc pro_test(@menberid int)asselect px=ROW_NUMBER()over(order by getdate()),t.OrderID,n.Address1,n.Address2,n.CountryName,n.ProviNce into #test from(select OrderID,MemberID,count(OrderID)over(partition by MemberID) as Totalfrom ORDERS)tinner join OrderShipping non t.OrderID=n.OrderIDwhere t.Total>=2 and t.MemberID=@menberiddeclare @px intselect @px=max(px) from #testif (select CountryName+ProviNce+Address1+Address2 from #test where px=@px) <>(select CountryName+ProviNce+Address1+Address2 from #test where px=@px-1)print '地址不一致为异常'else print '地址一致为正常'goexec pro_test 1/*地址不一致为异常*/--我只是比较了最后一个Province,没比较全部地址,这下应该对了吧
[解决办法]