查询语句:查两个表中的某向个字段数据_取两个表中一个字段中每个数据的数量-优快云博客

本文链接：https://blog.youkuaiyun.com/if_else123/article/details/6848601

本文介绍如何使用SQL语句实现高效去重、数据过滤和特定条件下的数据匹配，通过实例演示了如何从两个表中选择相同字段的数据，并进行过滤与去重操作，包括使用LEFT JOIN、CASE WHEN、TOP、DISTINCT等关键字的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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]