在SQL Server 2008 R2中选择语句 [英] Select Statement in sql server 2008 R2
问题描述
大家好，
我有5个项目(A，B，C，D，E).项目A是项目(B，C)的父亲.
在我的应用程序中，当我选择项目A时，它也会自动将项目(B，C)插入到数据网格中.
这意味着我只选择了项目A，但它在datagrid中显示了项目(A，B，C).
当我再添加两个项目(D，E).因此在datagrid中将显示5个项目(A，B，C，D，E)
当我在sql中使用selete语句时，它还会显示5个项目(A，B，C，D，E).
但是我的目的是我只想显示项目(A，D，E)，对于项目(B，C)它不能显示，因为它们是项目A的子项.
注意:
我有2张桌子
1.is销售表
2.用于商店父项和子项
这是表格结构
| table1 |
|---|
| ColumnName | DataType | Size |
|---|---|---|
| Code | int | |
| itemcode | varchar | 20 |
| 项目名称 | varchar | 50 |
| 价格 | 数字 | (18,2) |
| 数量 | int | |
| 金额 | 数字 | (18,2) |
| table2 |
|---|
| ColumnName | DataType | Size |
|---|---|---|
| FatherCode | varchar | 20 |
| ChildCode | varchar | 20 |
| 文档日期 | 日期时间 | 50 |
如何在sql中使用select语句仅显示父项而不显示子项?
谢谢
托尼
大家好，
这些是我的表结构以及我要显示的内容.
表1
| 物品代码 | 数量 |
|---|---|
| A | 5 |
| B | 1 |
| C | 3 |
| D | 4 |
| E | 5 |
| F | 6 |
| G | 1 |
| H | 3 |
| I | 8 |
| J | 5 |
表2
| 父亲代码 | 子代码 |
|---|---|
| A | B |
| A | C |
| D | E |
| D | F |
| D | G |
| H | I |
我要显示的内容.
| 物品代码 | 数量 |
|---|---|
| A | 5 |
| D | 4 |
| H | 3 |
| J | 5 |
为什么项目(B，C，E，F，G，I)不显示?因为它们是项目(A，D，H)的子项目，而项目(J)不是其他项目的孩子或父亲. br/>
谢谢
托尼
[/EDIT]
Hi all,
i have 5 item(A,B,C,D,E).Item A is a father of item (B,C).
In my application,when i select item A,it automatic insert item (B,C) to datagrid too.
It means that i selected only item A but it display item (A,B,C) in datagrid.
when i add two more item (D,E).So in datagrid will display 5 item (A,B,C,D,E)
When i use selete statement in sql, it is also display 5 item (A,B,C,D,E).
but i purpose is i want to display only item (A,D,E),For item (B,C) it can''t display because they are the child of item A.
Note:
i have 2 table
1.is sale table
2.is for store father item and child item
Here is the table stucture
| table1 |
|---|
| ColumnName | DataType | Size |
|---|---|---|
| Code | int | |
| itemcode | varchar | 20 |
| itemname | varchar | 50 |
| price | numeric | (18,2) |
| qty | int | |
| amount | numeric | (18,2) |
| table2 |
|---|
| ColumnName | DataType | Size |
|---|---|---|
| FatherCode | varchar | 20 |
| ChildCode | varchar | 20 |
| docdate | datetime | 50 |
How can i use select statement in sql to display only father item and not child item?
Thanks
TONY
Hi all,
These are my table structure and what i want to display.
TABLE1
| Item Code | Quantity |
|---|---|
| A | 5 |
| B | 1 |
| C | 3 |
| D | 4 |
| E | 5 |
| F | 6 |
| G | 1 |
| H | 3 |
| I | 8 |
| J | 5 |
TABLE2
| Father Code | Child Code |
|---|---|
| A | B |
| A | C |
| D | E |
| D | F |
| D | G |
| H | I |
What i want to display.
| Item Code | Quantity |
|---|---|
| A | 5 |
| D | 4 |
| H | 3 |
| J | 5 |
Why item(B,C,E,F,G,I) are not display?Because they are the child item of item (A,D,H) and item(J) is not child or father of other item.
Thanks
TONY
[/EDIT]
推荐答案
我不确定您拥有什么，但是下面的查询从table2中选择了所有非重复的父亲代码.
I''m not sure what you wnat, but the below query selects all non-duplicates FatherCodes from table2.
SELECT DISTINCT FatherCode
FROM table2
该查询从table2中选择所有父亲代码等于"A"的记录.
This query selects all records from table2 where FatherCode is equal ''A''.
SELECT *
FROM table2
WHERE FatherCode = 'A'
似乎表之间的关系不存在，除非它是table1.itemCode = table2.FatherCode
要基于上述关系从两个表中选择所有记录:
It looks like the relation between tables doesn''t exists, unless it is table1.itemCode = table2.FatherCode
To select all records from both tables based on above relation:
SELECT t1.*, t2.*
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.itemCode = t2.FatherCode
1个选项:
1 option:
SELECT DISTINCT t1.[ItemCode], t1.[Quantity]
FROM Table1 AS t1 RIGHT JOIN Table2 AS t2 ON t1.ItemCode = t2.FatherCode
2个选项:
2 option:
SELECT DISTINCT [ItemCode], [Quantity]
FROM Table1 AS t1
WHERE [ItemCode] IN (SELECT [FatherCode] AS [ItemCode]
FROM Table2)
[/EDIT]
[/EDIT]
这篇关于在SQL Server 2008 R2中选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
