解决方案 »
- 在sql 中的delete 语句中 用到表的别名会出错,为什么?
- 怎样能获取分数?
- 求SQL语句
- 求一个SQL语句
- mssql2k,两个相同数据库的比较
- 傳入表名和字段名,得到這個字段的數據類型,找了好久沒找到呀!
- 用ROW_NUMBER()实现分页,怎样才能得到分页总数
- 一个触发器的问题(IronPromises请也进来看看)
- 前不久,买了本SQL SERVER 2000,现在正在看,我怎么找不到SQL Server 2000 提供的Northwind里的Accounts,书上的实例就是用这个表的,好急
- 编写asp网页时,在打开数据库时总是出错?
- 怎么将字符串‘20150302123426000’转换为日期????
- 拆分SELECT语句,提取数值
ProductID 对应 SubProdID
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [Flag]
,[ProductID]
,[SubProdID]
,[QtyOfBatch]
,[BatchAmount] into #TempBom
FROM [CHIComp88].[dbo].[prdBOMMats] where ProductID='HCFWC11A.00000000000'
;WITH t AS
(
SELECT SubProdID,lvl=0
FROM #TempBom t
WHERE ProductID='HCFWC11A.00000000000'
UNION ALL
SELECT a.SubProdID,lvl+1
FROM #TempBom a
JOIN t b
ON a.ProductID = b.SubProdID
)
SELECT a.*,t.lvl
FROM #TempBom a
JOIN t
ON a.ProductID=t.SubProdID
--ORDER BY px drop table #TempBom为什么没有结果
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [Flag]
,[ProductID]
,[SubProdID]
,[QtyOfBatch]
,[BatchAmount] into #TempBom
FROM [CHIComp88].[dbo].[prdBOMMats] where ProductID='HCFWC11A.00000000000'
;WITH t AS
(
SELECT SubProdID,lvl=0
FROM #TempBom t
WHERE ProductID='HCFWC11A.00000000000'
UNION ALL
SELECT a.SubProdID,lvl+1
FROM #TempBom a
JOIN t b
ON a.SubProdID = b.ProductID)
SELECT a.*,t.lvl
FROM #TempBom a
JOIN t
ON a.ProductID=t.SubProdID
--ORDER BY px drop table #TempBom
列名 'ProductID' 无效。 --- T表里面不存在这样的 字段
是同一个问题,但是还是没有解决 ,我只能从新开个帖子, 又怕没有人更贴,只能这样了,sql新手,请教前面的我绕在 节段 里面去了
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [Flag]
,[ProductID]
,[SubProdID]
,[QtyOfBatch]
,[BatchAmount] into #TempBom
FROM [CHIComp88].[dbo].[prdBOMMats] where ProductID='HCFWC11A.00000000000'
;WITH t AS
(
SELECT *,lvl=0
FROM #TempBom t
WHERE ProductID='HCFWC11A.00000000000'
UNION ALL
SELECT a.*,lvl+1
FROM #TempBom a
JOIN t b
ON a.SubProdID = b.ProductID)
SELECT a.*,t.lvl
FROM #TempBom a
JOIN t
ON a.ProductID=t.SubProdID
--ORDER BY px drop table #TempBom
结果还是一样的,没有数据, 郁闷的我把 SELECT a.*,t.lvl
FROM #TempBom a
JOIN t
ON a.ProductID=t.SubProdID换成
SELECT *
FROM t
结果显示的 是 #TempBom 本身这就是为什么,没有数据的原因 #TempBom a JOIN #TempBom b on 条件 永远没有数据
SELECT 'HCFWC11A.00000000000',NULL,'HCFWC11A.1-100000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.00000000000',NULL,'HCFWC11A.1.100000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.00000000000',NULL,'HCFWC11A.1-200000000',2.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.00000000000',NULL,'HCFWC11A.1.200000000',2.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.00000000000',NULL,'HCFWC11A.1.300000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.00000000000',NULL,'HCFWC11A.1-300000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.100000000',NULL,'HCFWC11A.1.100000001',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.100000000',NULL,'SF-00000000000000002',0.20,0.1,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.100000001',NULL,'HCFWC11A.1.1-1000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.100000001',NULL,'HCFWC11A.1.1-2000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.100000001',NULL,'HCFWC11A.1.1-3000000',2.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.100000001',NULL,'HCFWC11AJL0000000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.200000000',NULL,'HCFWC11A.1.200000001',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.200000000',NULL,'SF-00000000000000002',0.004,0.1,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.200000001',NULL,'HCFWC11A.1.2-1000000',1.00,0,0,NULL,1.00 UNION ALL
SELECT 'HCFWC11A.1.200000001',NULL,'HCFWC11A.1.2-2000000',1.00,0,0,NULL,1.00
)
,BOM AS (
SELECT ProductID,
SubProdID,
Convert(varchar(max),ProductID+'\'+SubProdID) path
FROM prdBOMMats
WHERE ProductID = 'HCFWC11A.00000000000'
UNION ALL
SELECT p.ProductID,
p.SubProdID,
Convert(varchar(max),b.path+'\'+p.SubProdID) path
FROM BOM b
JOIN prdBOMMats p
ON b.SubProdID = p.ProductID
)
SELECT p.* --,b.path
FROM BOM b
JOIN prdBOMMats p
ON b.ProductID = p.ProductID
AND b.SubProdID = p.SubProdID
ORDER BY b.path
带 path 的结果
ProductID OrderID SubProdID QtyOfBatch WastingRate MatSource MatsRe BatchAmount path
-------------------- ----------- -------------------- ------------ ------------- ----------- ----------- ------------- ------------------------------------------------------------------------------------
HCFWC11A.00000000000 NULL HCFWC11A.1.100000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.100000000
HCFWC11A.1.100000000 NULL HCFWC11A.1.100000001 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.100000000\HCFWC11A.1.100000001
HCFWC11A.1.100000001 NULL HCFWC11A.1.1-1000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.100000000\HCFWC11A.1.100000001\HCFWC11A.1.1-1000000
HCFWC11A.1.100000001 NULL HCFWC11A.1.1-2000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.100000000\HCFWC11A.1.100000001\HCFWC11A.1.1-2000000
HCFWC11A.1.100000001 NULL HCFWC11A.1.1-3000000 2.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.100000000\HCFWC11A.1.100000001\HCFWC11A.1.1-3000000
HCFWC11A.1.100000001 NULL HCFWC11AJL0000000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.100000000\HCFWC11A.1.100000001\HCFWC11AJL0000000000
HCFWC11A.1.100000000 NULL SF-00000000000000002 0.200 0.1 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.100000000\SF-00000000000000002
HCFWC11A.00000000000 NULL HCFWC11A.1.200000000 2.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.200000000
HCFWC11A.1.200000000 NULL HCFWC11A.1.200000001 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.200000000\HCFWC11A.1.200000001
HCFWC11A.1.200000001 NULL HCFWC11A.1.2-1000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.200000000\HCFWC11A.1.200000001\HCFWC11A.1.2-1000000
HCFWC11A.1.200000001 NULL HCFWC11A.1.2-2000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.200000000\HCFWC11A.1.200000001\HCFWC11A.1.2-2000000
HCFWC11A.1.200000000 NULL SF-00000000000000002 0.004 0.1 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.200000000\SF-00000000000000002
HCFWC11A.00000000000 NULL HCFWC11A.1.300000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1.300000000
HCFWC11A.00000000000 NULL HCFWC11A.1-100000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1-100000000
HCFWC11A.00000000000 NULL HCFWC11A.1-200000000 2.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1-200000000
HCFWC11A.00000000000 NULL HCFWC11A.1-300000000 1.000 0.0 0 NULL 1.00 HCFWC11A.00000000000\HCFWC11A.1-300000000
膜拜,就是我想要的结果,谢谢我想问的是,这种sql 结构,csdn上面 哪里有或者 能不能 详细说下 什么意思
给你个详细说明的地址
http://www.cnblogs.com/lileiprc/articles/1869478.html