SELECT * FROM Cargo_SeaExport WHERE shippingdate = '2005-02-04' 或者 SELECT * FROM Cargo_SeaExport WHERE shippingdate = '2005-02-04' order by shippingdate 或者 SELECT * FROM Cargo_SeaExport WHERE shippingdate = '2005-02-04' order by code 都会出错 不知道为什么
SELECT ShippingOrderNo AS Code, SubBLNo AS MasterBill, (CASE ReferenceNo WHEN '' THEN ShippingOrderNo ELSE ReferenceNo END) AS ReferenceNo, ShippingDate, Quantity AS PCS, Volumn, Weight, (SELECT portnamee FROM port WHERE portno = shippingorder.loadingportno) AS Dep, mdg AS Dest, (SELECT objectnamec FROM operateobject WHERE objectno = shippingorder.agent) AS Agent, ISNULL ((SELECT income FROM ProfitSea WHERE shippingorderno = ShippingOrder.ShippingOrderNo), 0) AS Income, ISNULL ((SELECT Expend FROM ProfitSea WHERE shippingorderno = ShippingOrder.ShippingOrderNo), 0) AS Cost, ISNULL ((SELECT Profit FROM ProfitSea WHERE shippingorderno = ShippingOrder.ShippingOrderNo), 0) AS Profit FROM ShippingOrder WHERE (ShippingOrderNo NOT LIKE 'YUDAWH%') AND (ShippingOrderNo NOT LIKE 'NNR-I%') AND (ShippingOrderNo NOT LIKE 'YUDAI%') AND (ShippingOrderNo NOT IN (SELECT shippingorderno FROM shippingstate WHERE businessflag = '1'))
--删除重复数据一、具有主键的情况 a.具有唯一性的字段id(为唯一主键) delect table where id not in ( select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用来判断重复的条件,如只有col1, 那么只要col1字段内容相同即表示记录相同。b.具有联合主键 假设col1+','+col2+','...col5 为联合主键 select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table where having count(*)>1 group by col1,col2,col3,col4 ) group by 子句后跟的字段就是你用来判断重复的条件, 如只有col1,那么只要col1字段内容相同即表示记录相同。 or select * from table where exists (select 1 from table x where table.col1 = x.col1 and table.col2= x.col2 group by x.col1,x.col2 having count(*) >1)c:判断所有的字段 select * into #aa from table group by id1,id2,.... delete table insert into table select * from #aa二、没有主键的情况a:用临时表实现 select identity(int,1,1) as id,* into #temp from ta delect #temp where id not in ( select max(id) from # group by col1,col2,col3... ) delete table ta inset into ta(...) select ..... from #tempb:用改变表结构(加一个唯一字段)来实现 alter table 表 add newfield int identity(1,1) delete 表 where newfield not in ( select min(newfield) from 表 group by 除newfield外的所有字段 )alter table 表 drop column newfield
FROM Cargo_SeaExport
WHERE shippingdate = '2005-02-04'
或者
SELECT *
FROM Cargo_SeaExport
WHERE shippingdate = '2005-02-04'
order by shippingdate
或者
SELECT *
FROM Cargo_SeaExport
WHERE shippingdate = '2005-02-04'
order by code
都会出错
不知道为什么
(CASE ReferenceNo WHEN '' THEN ShippingOrderNo ELSE ReferenceNo END)
AS ReferenceNo, ShippingDate, Quantity AS PCS, Volumn, Weight,
(SELECT portnamee
FROM port
WHERE portno = shippingorder.loadingportno) AS Dep, mdg AS Dest,
(SELECT objectnamec
FROM operateobject
WHERE objectno = shippingorder.agent) AS Agent, ISNULL
((SELECT income
FROM ProfitSea
WHERE shippingorderno = ShippingOrder.ShippingOrderNo), 0) AS Income,
ISNULL
((SELECT Expend
FROM ProfitSea
WHERE shippingorderno = ShippingOrder.ShippingOrderNo), 0) AS Cost,
ISNULL
((SELECT Profit
FROM ProfitSea
WHERE shippingorderno = ShippingOrder.ShippingOrderNo), 0) AS Profit
FROM ShippingOrder
WHERE (ShippingOrderNo NOT LIKE 'YUDAWH%') AND
(ShippingOrderNo NOT LIKE 'NNR-I%') AND (ShippingOrderNo NOT LIKE 'YUDAI%') AND
(ShippingOrderNo NOT IN
(SELECT shippingorderno
FROM shippingstate
WHERE businessflag = '1'))
又不能用ORDER BY
a.具有唯一性的字段id(为唯一主键)
delect table
where id not in
(
select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,
那么只要col1字段内容相同即表示记录相同。b.具有联合主键
假设col1+','+col2+','...col5 为联合主键
select * from table where col1+','+col2+','...col5 in (
select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,
如只有col1,那么只要col1字段内容相同即表示记录相同。
or
select * from table where exists (select 1 from table x where table.col1 = x.col1 and
table.col2= x.col2 group by x.col1,x.col2 having count(*) >1)c:判断所有的字段
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa二、没有主键的情况a:用临时表实现
select identity(int,1,1) as id,* into #temp from ta
delect #temp
where id not in
(
select max(id) from # group by col1,col2,col3...
)
delete table ta
inset into ta(...)
select ..... from #tempb:用改变表结构(加一个唯一字段)来实现
alter table 表 add newfield int identity(1,1)
delete 表
where newfield not in
(
select min(newfield) from 表 group by 除newfield外的所有字段
)alter table 表 drop column newfield