CREATE VIEW dbo.DOR
AS
SELECT DISTINCT
TOP 100 PERCENT AJCONK AS 订单行, AJPRLV AS 处理级, AJPRNO AS 物料号
,(select top 1 right(物料号,4) from MIPHED where 处理级=16) as 钢种
FROM dbo.MIPHED
WHERE (LEFT(AJPRLV, 1) <> RIGHT(AJPRLV, 1)) AND AJCONK = '8200400057/020'
AND (AJPRNO <> '10sw-vd')
ORDER BY AJPRLV
AS
SELECT DISTINCT
TOP 100 PERCENT AJCONK AS 订单行, AJPRLV AS 处理级, AJPRNO AS 物料号
,(select top 1 right(物料号,4) from MIPHED where 处理级=16) as 钢种
FROM dbo.MIPHED
WHERE (LEFT(AJPRLV, 1) <> RIGHT(AJPRLV, 1)) AND AJCONK = '8200400057/020'
AND (AJPRNO <> '10sw-vd')
ORDER BY AJPRLV
解决方案 »
- 脑壳短路了,求查询汇总语句
- 同样的slq语句,在不同电脑上的执行时间为什么差距这么大
- SQL2000问题
- Reporting Services 参数有默认值时会自动执行
- 请问win98怎么装SQL server2000呢????
- 我碰到一道题,现在玩多了ORACLE都玩不来FOXPRO乐,请各位帮帮忙,100分相送,谢谢
- 关于存储过程中游标的问题——无法定义。
- 请教:vs.net(中文版)应该含有vfp7,可我安装后却没有,只有vb,vc,vc++,vc#,是否安装不对?应如何安装?
- 谁有SQL的to_date()函数的参数表
- 触发器问题,请高手指点
- #############请教一个查询问题?#############
- 字段问题
case
when 处理级=16 then 钢种=right(物料号,4)
when ......... then ............
else
end
AS
SELECT DISTINCT
TOP 100 PERCENT A.AJCONK AS 订单行, A.AJPRLV AS 处理级, A.AJPRNO AS 物料号,B.钢种
FROM dbo.MIPHED A
INNER JOIN
(SELECT AJCONK AS 订单行 ,RIGHT(钢种,4) AS 钢种
FROM dbo.MIPHED
WHERE 处理级='16'
GROUP BY 订单行 ,RIGHT(钢种,4)
) AS
B ON B.订单行=A.订单行
WHERE (LEFT(AJPRLV, 1) <> RIGHT(AJPRLV, 1)) AND AJCONK = '8200400057/020'
AND (AJPRNO <> '10sw-vd')
ORDER BY AJPRLV
AS
SELECT DISTINCT TOP 100 PERCENT AJCONK AS 订单行, AJPRLV AS 处理级, AJPRNO AS 物料号
,b.钢种
FROM dbo.MIPHED a,(
select top 1 钢种=right(物料号,4) from MIPHED where 处理级=16)b
WHERE AJCONK = '8200400057/020'
and AJPRNO <> '10sw-vd'
and LEFT(AJPRLV,1) <> RIGHT(AJPRLV,1)
ORDER BY AJPRLV
我用这个:CREATE VIEW dbo.DOR
AS
SELECT DISTINCT
TOP 100 PERCENT AJCONK AS 订单行, AJPRLV AS 处理级, AJPRNO AS 物料号
,(select top 1 right(AJPRNO,4) from MIPHED where AJPRLV=16) as 钢种
FROM dbo.MIPHED
WHERE (LEFT(AJPRLV, 1) <> RIGHT(AJPRLV, 1)) AND AJCONK = '8200400057/020'
AND (AJPRNO <> '10sw-vd')
ORDER BY AJPRLV结果 钢种 那列 全是空的
这样结果是不对的
AJCONK = '8200400057/020'的,而且是要按 AJCONK分开去得到钢种
订单行 处理级 物料号
8200400057/020 16 113101160
8200400057/020 26 2C24450894K550B
8200400057/020 38 9N26992100K550B
8200400057/020 41 9C24450894K550B
.......
8200400058/020 16 113103330
8200400058/020 26 2C24450894K550B
8200400058/020 38 9N26992100K550B
8200400058/020 41 9C24450894K550B要的结果应该是订单行 处理级 物料号 钢种
8200400057/020 16 113101160 1160
8200400057/020 26 2C24450894K550B 1160
8200400057/020 38 9N26992100K550B 1160
8200400057/020 41 9C24450894K550B 1160
.......
8200400058/020 16 113103330 3330
8200400058/020 26 2C24450894K550B 3330
8200400058/020 38 9N26992100K550B 3330
8200400058/020 41 9C24450894K550B 3330
AS
SELECT DISTINCT TOP 100 PERCENT a.AJCONK AS 订单行,a.AJPRLV AS 处理级,a.AJPRNO AS 物料号
,b.钢种
FROM MIPHED a join (
select AJCONK,钢种=right(min(AJPRNO),4)
from MIPHED where AJPRLV=16
group by AJCONK
)b on a.AJCONK=b.AJCONK
WHERE a.AJCONK = '8200400057/020'
and a.AJPRNO <> '10sw-vd'
and LEFT(a.AJPRLV,1) <> RIGHT(a.AJPRLV,1)
ORDER BY a.AJPRLV
CREATE VIEW DOR
AS
SELECT DISTINCT TOP 100 PERCENT a.AJCONK AS 订单行,a.AJPRLV AS 处理级,a.AJPRNO AS 物料号
,b.钢种
FROM MIPHED a join (
select AJCONK,钢种=right(AJPRNO,4)
from MIPHED where AJPRLV=16
)b on a.AJCONK=b.AJCONK
WHERE a.AJCONK = '8200400057/020'
and a.AJPRNO <> '10sw-vd'
and LEFT(a.AJPRLV,1) <> RIGHT(a.AJPRLV,1)
ORDER BY a.AJPRLV
from MIPHED where AJPRLV=16
group by AJCONK也就是这句,单独的运行,就没有值的
create table MIPHED(AJCONK char(14),AJPRLV int,AJPRNO varchar(20))
insert MIPHED select '8200400057/020',16,'113101160'
union all select '8200400057/020',26,'2C24450894K550B'
union all select '8200400057/020',38,'9N26992100K550B'
union all select '8200400057/020',41,'9C24450894K550B'
union all select '8200400058/020',16,'113103330'
union all select '8200400058/020',26,'2C24450894K550B'
union all select '8200400058/020',38,'9N26992100K550B'
union all select '8200400058/020',41,'9C24450894K550B'
go--如果第个订单行,处理级=16的只有一条,可以改为:
CREATE VIEW DOR
AS
SELECT DISTINCT TOP 100 PERCENT a.AJCONK AS 订单行,a.AJPRLV AS 处理级,a.AJPRNO AS 物料号
,b.钢种
FROM MIPHED a join (
select AJCONK,钢种=right(AJPRNO,4)
from MIPHED where AJPRLV=16
)b on a.AJCONK=b.AJCONK
WHERE a.AJCONK = '8200400057/020'
and a.AJPRNO <> '10sw-vd'
and LEFT(a.AJPRLV,1) <> RIGHT(a.AJPRLV,1)
ORDER BY a.AJPRLV
goselect * from dor
godrop table MIPHED
drop view dor/*--测试结果
订单行 处理级 物料号 钢种
-------------- ----------- -------------------- --------
8200400057/020 16 113101160 1160
8200400057/020 26 2C24450894K550B 1160
8200400057/020 38 9N26992100K550B 1160
8200400057/020 41 9C24450894K550B 1160(所影响的行数为 4 行)
--*/
CREATE VIEW DOR
AS
SELECT DISTINCT TOP 100 PERCENT a.AJCONK AS 订单行,a.AJPRLV AS 处理级,a.AJPRNO AS 物料号
,b.钢种
FROM MIPHED a join (
select AJCONK,钢种=right(rtrim(AJPRNO),4)
from MIPHED where AJPRLV=16
)b on a.AJCONK=b.AJCONK
WHERE a.AJCONK = '8200400057/020'
and a.AJPRNO <> '10sw-vd'
and LEFT(a.AJPRLV,1) <> RIGHT(a.AJPRLV,1)
ORDER BY a.AJPRLV