查询语句:查两个表中的某向个字段数据

来源：互联网发布：霸刀战神翅膀升级数据编辑：程序博客网时间：2024/05/28 15:08

T_APP_PRODUCT 和T_AUDIT_PRODUCT 是两个表,查出它们中相同字段的数据,且过滤重复的数据.

SELECT
case when b.PRODUCT_NAME is not null then b.PRODUCT_NAME else a.PRODUCT_NAME end PRODUCT_NAME ,
case when b.PRODUCT_SUMMARY is not null then b.PRODUCT_SUMMARY else a.PRODUCT_SUMMARY end PRODUCT_SUMMARY,
case when b.ITEMNUM is not null then b.ITEMNUM else a.ITEMNUM end ITEMNUM,
a.TAP_ID, a.AUDIT_STATUS, a.REMARK
FROM CAM.T_APP_PRODUCT a left join T_AUDIT_PRODUCT b on a.TAP_ID = b.TAP_ID where a.TAB_ID = 'ffc27d804b4f11dfa86c001e4fd738b8'

--索引为31-40的数据
select top 10 * from orderitems where ID not in (select top 30 ID from orderitems)

--复制数据
insert into distinctselect(UserName,State,OrderTime) select UserName,State,OrderTime from orderitems

--去除所有重复的记录(完全重复)
select distinct UserName ,ID, State,OrderTime into #Table1 from [distinctselect]
delete from [distinctselect]
insert into [distinctselect](UserName,State,OrderTime) select UserName,State,OrderTime from #Table1
drop table #Table1

--删除某列重复的记录
delete   t
from   [distinctselect]   t
where   exists   (
select   1   from   [distinctselect] where   username=t.username   and   id<t.id)

select * from [distinctselect] order by username

delete from [distinctselect]