获取特定记录的问题 [英] Problem getting specific record
问题描述
嗨专家,
我有一个问题,那就是:我有两张桌子
类别<前lang =text> Cat_Id(PK)Cat_Name
1 abc
2 Kar
3 Isb
物品
Item_ID(PK)Item_Name Cat_Id(FK)
1 Item1 2
2 Item2 1
3 Item3 1
4 Item4 3
5 Item5 2
i想显示来自物品的数据基于Cat_Id(FK)..
例如:
类别1
项目2
项目3
类别2
项目1
项目5
类别ory 3
Item4
如何在一个Sql查询/程序中执行此操作....我试过了足够自己有不同的条件和逻辑但是徒劳....
请帮助我..
提前使用...
试试这个:
DECLARE @ cat TABLE (Cat_Id < span class =code-keyword> INT IDENTITY ( 1 , 1 ),Cat_Name NVARCHAR ( 30 ))
INSERT INTO @ cat (Cat_Name)
VALUES (' abc ')
INSERT INTO @ cat (Cat_Name)
VALUES (' Kar')
INSERT INTO @ cat (Cat_Name)
VALUES (' Isb')
DECLARE @ itm 表(Item_ID INT IDENTITY ( 1 , 1 ),Item_Name NVARCHAR ( 30 ),Cat_Id INT )
INSERT 跨度> INTO @ itm (Item_Name,Cat_Id)
VALUES (' Item1', 2 )
INSERT INTO @ itm (Item_Name,Cat_Id)
VALUES (' Item2', 1 )
INSERT INTO @ itm (Item_Name,Cat_Id)
VALUES (' Item3', 1 )
INSERT INTO @ itm (Item_Nam e,Cat_Id)
VALUES (' Item4 ', 3 )
INSERT INTO @ itm (Item_Name,Cat_Id)
VALUES (' Item5', 2 )
SELECT c.Cat_Name,i.Item_Name
FROM @ cat AS c RIGHT JOIN @ itm AS i ON c.Cat_Id = i.Cat_Id
ORDER BY c.Cat_Name
; WITH allitems AS
(
SELECT Cat_Id,' ===' + Cat_Name + ' ===' AS [CategoriesAndItems], 0 作为 Item_Id
FROM @ cat
UNION ALL
SELECT Cat_Id,Item_Name AS [CategoriesAndItems],Item_Id
FROM @ itm
)
SELECT [CategoriesAndItems]
FROM allitems
ORDERBY Cat_id,Item_Id
SELECT语句返回:
abc Item2
abc Item3
Isb Item4
Kar Item5
Kar Item1
CTE返回:
=== abc ===
Item2
Item3
= == Kar ===
Item1
Item5
=== Isb ===
Item4
更多:
使用公用表表达式 [ ^ ]
UNIONA(T-SQL) [ ^ ]
选择 c.Cat_Id ,i.Item_Name 来自项目i 内部 加入类别c on c.Cat_Id = i.Cat_Id 其中 c.Cat_Id
(选择 Cat_Id 来自类别)
Hi Experts,
i''ve a problem and that is : i''ve two tables
Category
Cat_Id(PK) Cat_Name
1 abc
2 Kar
3 Isb
Items
Item_ID(PK) Item_Name Cat_Id(FK)
1 Item1 2
2 Item2 1
3 Item3 1
4 Item4 3
5 Item5 2
i want to display data from "Items" on the basis of Cat_Id(FK)..
For Example:
Category 1
Item2
Item3
Category 2
Item1
Item5
Category 3
Item4
How can i do this in one Sql query/Procedure.... I''ve tried enough myself with different conditions and logics but in vain....
Please Help me..
Thanx in Advance...
Try this:
DECLARE @cat TABLE (Cat_Id INT IDENTITY(1,1), Cat_Name NVARCHAR(30)) INSERT INTO @cat (Cat_Name) VALUES('abc') INSERT INTO @cat (Cat_Name) VALUES('Kar') INSERT INTO @cat (Cat_Name) VALUES('Isb') DECLARE @itm TABLE (Item_ID INT IDENTITY(1,1), Item_Name NVARCHAR(30), Cat_Id INT) INSERT INTO @itm (Item_Name, Cat_Id) VALUES('Item1', 2) INSERT INTO @itm (Item_Name, Cat_Id) VALUES('Item2', 1) INSERT INTO @itm (Item_Name, Cat_Id) VALUES('Item3', 1) INSERT INTO @itm (Item_Name, Cat_Id) VALUES('Item4', 3) INSERT INTO @itm (Item_Name, Cat_Id) VALUES('Item5', 2) SELECT c.Cat_Name, i.Item_Name FROM @cat AS c RIGHT JOIN @itm AS i ON c.Cat_Id = i.Cat_Id ORDER BY c.Cat_Name ;WITH allitems AS ( SELECT Cat_Id, '===' + Cat_Name + '===' AS [CategoriesAndItems], 0 As Item_Id FROM @cat UNION ALL SELECT Cat_Id, Item_Name AS [CategoriesAndItems], Item_Id FROM @itm ) SELECT [CategoriesAndItems] FROM allitems ORDER BY Cat_id, Item_Id
SELECT statement returns:
abc Item2 abc Item3 Isb Item4 Kar Item5 Kar Item1
CTE returns:
===abc=== Item2 Item3 ===Kar=== Item1 Item5 ===Isb=== Item4
select c.Cat_Id,i.Item_Name from items i inner join category c on c.Cat_Id=i.Cat_Id where c.Cat_Id in (select Cat_Id from category)
这篇关于获取特定记录的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!