我想查询根据每个customer_part_number,cp_rev 找出dept_name字段中"钻孔" 在 "字符"后面出现的customer_part_number,cp_rev,根据下面的条件,查询出来的数据为:
customer_part_number cp_rev
P01AL005501 00
--sql 语句如下
create table #tmp
(
customer_part_number varchar(20),
cp_rev varchar(5),
step_number int,
dept_code char(3),
dept_name varchar(20)
)insert into #tmp
select 'P01AL005501','00',1,'C05','压合'
union all
select 'P01AL005501','00',2,'P01','单面湿绿油'
union all
select 'P01AL005501','00',3,'G03','字符'
union all
select 'P01AL005501','00',4,'B01','钻孔'
union all
select 'P01AL005501','0A',1,'C05','压合'
union all
select 'P01AL005501','0A',2,'P01','单面湿绿油'
union all
select 'P01AL005501','0A',3,'G03','钻孔'
union all
select 'P01AL005501','0A',4,'I01','V-CUT'
union all
select 'P01AL005501','0A',5,'B01','字符' --后面union all语句 省略......
解决方案 »
- sql server jdbc问题
- 求解!两个表连接
- function中插入表数据返回"Error 443 invalid use of 'INSERT' within a function",难道不行吗?
- 如何对float、moeny、int等类型字段进行数据清零?
- 没法设主键怎么办啊?
- 这段代码执行结果很郁闷
- 关于SQL安装的问题
- if else 条件转化语句在函数中怎么用?
- ms sql 快把我磨到没信心的问题.......求救
- msSQL更新数据问题(非简单更新)
- 让搜索结果按搜索条件排
- |zyciis| 一个品牌表,一个类型表,现在添加一个类型拥有品牌表,那应该不应该给他加多字段唯一索引 谢谢
and step_number<t.step_number and dept_name='钻孔' ) and dept_name ='字符'
WHERE T.DEPT_NAME IN ('字符','钻孔')
AND NOT EXISTS(SELECT NULL FROM #TMP WHERE T.CUSTOMER_PART_NUMBER=#TMP.CUSTOMER_PART_NUMBER
AND #TMP.STEP_NUMBER<T.STEP_NUMBER AND #TMP.DEPT_NAME='钻孔')
AND DEPT_NAME ='钻孔'customer_part_number cp_rev step_number dept_code dept_name
-------------------- ------ ----------- --------- --------------------
P01AL005501 0A 3 G03 钻孔(1 row(s) affected)
SELECT * FROM #TMP T
WHERE T.DEPT_NAME IN ('字符','钻孔')
AND NOT EXISTS(SELECT NULL FROM #TMP WHERE T.CUSTOMER_PART_NUMBER=#TMP.CUSTOMER_PART_NUMBER
and #TMP.cp_rev<T.cp_rev
AND #TMP.STEP_NUMBER<T.STEP_NUMBER AND #TMP.DEPT_NAME='钻孔')
AND DEPT_NAME ='钻孔'