首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

求解一个疑难的SQL语句解决思路

2012-06-08 
求解一个疑难的SQL语句CREATE TABLE ORDERS(OrderID VARCHAR(50),MemberID VARCHAR(100),CREATEON DATETIM

求解一个疑难的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做比较 得出来结果为地址不一致为异常


[解决办法]

SQL code
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/*地址一致为正常*/
[解决办法]
SQL code
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,没比较全部地址,这下应该对了吧
[解决办法]
探讨



嗯,谢谢啊, select @px=max(px) from #test
if (select CountryName+ProviNce+Address1+Address2 from #test where px=@px)
<>(select CountryName+ProviNce+Address1+Address2 from #test where px=@px-1)
SQL还……

热点排行