建議在查詢語句中處理SELECT DISTINCT au_id FROM titleauthor來將重復的去掉!
qrreport生成這種格式有點難度; 水晶報表我沒有用過;我的建議是放棄
试下, 如有Table1 ItemID, ItemTypeID,项目类别(ItemType) 项目名称(ItemName) 1 1 外观 长度 2 1 外观 宽度 3 2 温度 里面温度 4 2 温度 外面温度说明:ItemID为自动编号 ItemTypeID 为类别ID号 //建立存储过程如下: CREATE PROCEDURE PROCEDURE1 ASSELECT DISTINCT ItemTypeID,ItemType INTO #TMP01 FROM table1CREATE TABLE #Result( [ItemID][int], [TypeID][int], [ItemTypeID][int], [ItemType][varchar](50), [ItemName][varchar](50), )DECLARE @TypeID int,@ItemtypeID int,@ItemType varchar(50),@ItemName Varchar(50)SELECT @TypeID=0DECLARE Item_Cursor SCROLL CURSOR FOR SELECT ItemType,ItemtypeID FROM #TMP01 OPEN Item_Cursor FETCH Item_Cursor INTO @ItemType,@ItemtypeID WHILE @@FETCH_STATUS=0 BEGIN INSERT #Result(TypeID,ItemID,ItemTypeID,ItemType,ItemName) SELECT TOP 1 @TypeID,ItemID,@ItemtypeID,@ItemType,ItemName FROM table1 WHERE ItemType=@ItemType
FETCH Item_Cursor INTO @ItemType,@ItemtypeID END CLOSE Item_Cursor DEALLOCATE Item_CursorINSERT INTO #Result(TypeID,ItemID,ItemTypeID,ItemType,ItemName) SELECT 1,ItemID,ItemtypeID,ItemType,ItemName FROM table1 WHERE ItemID NOT IN (SELECT itemID from #Result)UPDATE #Result SET ItemType=NULL WHERE TypeID<>0SELECT ItemType,ItemName FROM #Result ORDER BY ItemTypeID,TypeID调用存储过程 Exec Procedure1 得如下结果: ItemType ItemName 外观 长度 NULL 宽度 温度 里面温度 NULL 外面温度
FROM titleauthor來將重復的去掉!
水晶報表我沒有用過;我的建議是放棄
如有Table1
ItemID, ItemTypeID,项目类别(ItemType) 项目名称(ItemName)
1 1 外观 长度
2 1 外观 宽度
3 2 温度 里面温度
4 2 温度 外面温度说明:ItemID为自动编号
ItemTypeID 为类别ID号
//建立存储过程如下:
CREATE PROCEDURE PROCEDURE1
ASSELECT DISTINCT ItemTypeID,ItemType INTO #TMP01 FROM table1CREATE TABLE #Result(
[ItemID][int],
[TypeID][int],
[ItemTypeID][int],
[ItemType][varchar](50),
[ItemName][varchar](50),
)DECLARE @TypeID int,@ItemtypeID int,@ItemType varchar(50),@ItemName Varchar(50)SELECT @TypeID=0DECLARE Item_Cursor SCROLL CURSOR FOR SELECT ItemType,ItemtypeID FROM #TMP01
OPEN Item_Cursor
FETCH Item_Cursor INTO @ItemType,@ItemtypeID
WHILE @@FETCH_STATUS=0
BEGIN INSERT #Result(TypeID,ItemID,ItemTypeID,ItemType,ItemName)
SELECT TOP 1 @TypeID,ItemID,@ItemtypeID,@ItemType,ItemName FROM table1 WHERE ItemType=@ItemType
FETCH Item_Cursor INTO @ItemType,@ItemtypeID
END
CLOSE Item_Cursor
DEALLOCATE Item_CursorINSERT INTO #Result(TypeID,ItemID,ItemTypeID,ItemType,ItemName)
SELECT 1,ItemID,ItemtypeID,ItemType,ItemName FROM table1
WHERE ItemID NOT IN (SELECT itemID from #Result)UPDATE #Result SET ItemType=NULL WHERE TypeID<>0SELECT ItemType,ItemName FROM #Result ORDER BY ItemTypeID,TypeID调用存储过程
Exec Procedure1
得如下结果:
ItemType ItemName
外观 长度
NULL 宽度
温度 里面温度
NULL 外面温度