艰难的Sql查询 [英] Tough Sql Query
问题描述
我对这个查询很生气。我需要加入3个表格。他们的格式
优惠券
[优惠券] [uniqueidentifier]非空，
[ VoucherTypeID] [int] NOT NULL，
[VoucherNo] [int] NULL，
[VoucherDate] [datetime] NOT NULL，
[ VoucherNarration] [varchar]（255）
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
（
[VoucherID]
）ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY（1,1）NOT NULL，
[LedgerName] [ varchar]（50）COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
（
[LedgerID]
） ON [PRIMARY]
CREATE TABLE [Transactions]（
[TransactionID] [uniqueidentifier] NOT NULL，
[VoucherID] [uniqueidentifier] NOT NULL，
[ByTo] [char]（1）
[LedgerID] [int] NOT NULL，
[Credit] [money] NOT NULL，
[借记] [钱] NOT NULL，
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTER ED
（
[TransactionID]
）ON [PRIMARY]，
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
（
[LedgerID]
）参考文献[Ledgers]（
[LedgerID]
），
CONSTRAINT [FK_Transactions_Vouchers]外键
（
[代金券]
）参考文献[代金券] ]（
[代金券]
）
）ON [主要]
GO
所需产出为
ID凭证号凭证日期名额金额
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03现金600.00
3 3 2001-09-03税收A / C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
但是，我从交易表中得到多行。我只是
需要第一个匹配的行
ID凭证号凭证日期LedgerName金额
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03现金600.00
3 3 2001-09-03税收A / C 0.00
4 4 2001- 09-03 Bank-1 4000.00
5 4 2001-09-03现金400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03现金5035.00
我正在使用的查询是
SELECT dbo.Vouchers2001.VoucherID，
dbo.Vouchers2001.VoucherNo，
dbo.Vouchers2001.VoucherDate，
dbo.Ledgers.LedgerName，
SUM（dbo .Transactions2001.Debit）AS金额
来自dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE（dbo.Vouchers2001.VoucherTypeID = 1）
GROUP BY dbo.Vouchers2001.VoucherID，
dbo.Ledgers.LedgerName，
dbo.Vouchers2001.VoucherDate，
dbo.Vouchers2001.VoucherNo，
dbo.Vouchers2001 .VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID，
dbo.Ledgers.LedgerName，
dbo.Vouchers2001.VoucherDate，
dbo.Vouchers2001.VoucherNo
Plz帮助Out
***通过Developersdex发送 http://www.developersdex.com ***
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.developersdex.com ***
推荐答案
结果集中的第一列是ID，但是你不会说ID
来自哪个，因为任何表中都没有这样的列。另外，你没有
提供样本数据的INSERT语句。
-
Tom
--------------------------------------------- -------
Thomas A. Moreau，理学士，博士，MCSE，MCDBA
SQL Server MVP
专栏作家，SQL Server专业
加拿大多伦多
www.pinpub.com
..
" Bill Bob" <无**** @ devdex.com>在消息中写道
新闻：MT **************** @ news.uswest.net ...
我要去对这个查询很生气。我需要加入3个表格。他们的格式
优惠券
[优惠券] [uniqueidentifier]非空，
[ VoucherTypeID] [int] NOT NULL，
[VoucherNo] [int] NULL，
[VoucherDate] [datetime] NOT NULL，
[ VoucherNarration] [varchar]（255）
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
（
[VoucherID]
）ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY（1,1）NOT NULL，
[LedgerName] [ varchar]（50）COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
（
[LedgerID]
） ON [PRIMARY]
CREATE TABLE [Transactions]（
[TransactionID] [uniqueidentifier] NOT NULL，
[VoucherID] [uniqueidentifier] NOT NULL，
[ByTo] [char]（1）
[LedgerID] [int] NOT NULL，
[Credit] [money] NOT NULL，
[借记] [钱] NOT NULL，
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTER ED
（
[TransactionID]
）ON [PRIMARY]，
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
（
[LedgerID]
）参考文献[Ledgers]（
[LedgerID]
），
CONSTRAINT [FK_Transactions_Vouchers]外键
（
[代金券]
）参考文献[代金券] ]（
[代金券]
）
）ON [主要]
GO
所需产出为
ID凭证号凭证日期名额金额
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03现金600.00
3 3 2001-09-03税收A / C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
但是，我从交易表中得到多行。我只是
需要第一个匹配的行
ID凭证号凭证日期LedgerName金额
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03现金600.00
3 3 2001-09-03税收A / C 0.00
4 4 2001- 09-03 Bank-1 4000.00
5 4 2001-09-03现金400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03现金5035.00
我正在使用的查询是
SELECT dbo.Vouchers2001.VoucherID，
dbo.Vouchers2001.VoucherNo，
dbo.Vouchers2001.VoucherDate，
dbo.Ledgers.LedgerName，
SUM（dbo .Transactions2001.Debit）AS金额
来自dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE（dbo.Vouchers2001.VoucherTypeID = 1）
GROUP BY dbo.Vouchers2001.VoucherID，
dbo.Ledgers.LedgerName，
dbo.Vouchers2001.VoucherDate，
dbo.Vouchers2001.VoucherNo，
dbo.Vouchers2001 .VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID，
dbo.Ledgers.LedgerName，
dbo.Vouchers2001.VoucherDate，
dbo.Vouchers2001.VoucherNo
Plz帮助Out
***通过Developersdex发送 http://www.developersdex.com ***
The first column in the resultset is ID, but you don''t say from which the ID
originates, since there is no such column in any table. Also, you did not
provide INSERT statements of sample data.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Bill Bob" <no****@devdex.com> wrote in message
news:MT****************@news.uswest.net...
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.developersdex.com ***
优惠券表
7332429B-22BF-49B8-A5B6-00094FF9DD59 4 1
2001- 07-04 19：54：00.000
26D65B5D-E389-4E74-8605-000EC9F10575 14 1
2001-07-30 15：49：00.000
A40996E6-0581-48D9-91A9-000F1E0D0B46 14 1
2001- 09-17 15：23：00.000
6EE61DEC-C157-4371-AAD3-0018B70B1A96 4 1
2001-09-15 21：48：00.000
267E5D45-F38D-4FB8-A2DF-00210BF9D037 4 1
2001-06-04 18：28：00.000
交易表
CBBD8EBE-55BA-4039-9C3B-0537FE348470
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB B 1 .0000
600.0000
836E2414-6E6B-4608-BF63-0A15DBD540DA
96C2D6F7-C3EF-4A92-9861-EA8E99E2D297 T 25 48.0000
..0000
3221E16C -CB2E-487E-A875-4613BAFB40D2
4A181205-67B7-4B38-A9C8-D416ACECA978 B 1 .0000
.5000
1736F5A0- EBE3-4494-B075-52216E73E857
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB T 1 2.0000
..0000
B475F07A-7012- 4DC8-B0C9-7BE8A66493C8
4A181205-67B7-4B38-A9C8-D416ACECA978 B 232 .0000
.5000
Ledger表
1 Ca. sh $ / $
2利润&损失A / c
3分行-1
4预留保证金1 /
5 DutiesTaxes-1
***通过开发人员指南 http://www.developersdex.com 发送***
Voucher Table
7332429B-22BF-49B8-A5B6-00094FF9DD59 4 1
2001-07-04 19:54:00.000
26D65B5D-E389-4E74-8605-000EC9F10575 14 1
2001-07-30 15:49:00.000
A40996E6-0581-48D9-91A9-000F1E0D0B46 14 1
2001-09-17 15:23:00.000
6EE61DEC-C157-4371-AAD3-0018B70B1A96 4 1
2001-09-15 21:48:00.000
267E5D45-F38D-4FB8-A2DF-00210BF9D037 4 1
2001-06-04 18:28:00.000
Transactions Table
CBBD8EBE-55BA-4039-9C3B-0537FE348470
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB B 1 .0000
600.0000
836E2414-6E6B-4608-BF63-0A15DBD540DA
96C2D6F7-C3EF-4A92-9861-EA8E99E2D297 T 25 48.0000
..0000
3221E16C-CB2E-487E-A875-4613BAFB40D2
4A181205-67B7-4B38-A9C8-D416ACECA978 B 1 .0000
..5000
1736F5A0-EBE3-4494-B075-52216E73E857
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB T 1 2.0000
..0000
B475F07A-7012-4DC8-B0C9-7BE8A66493C8
4A181205-67B7-4B38-A9C8-D416ACECA978 B 232 .0000
..5000
Ledger Table
1 Cash
2 Profit & Loss A/c
3 Branch-1
4 ReserveSurplus-1
5 DutiesTaxes-1
*** Sent via Developersdex http://www.developersdex.com ***
是否可以将这些作为INSERT VALUES语句获取？
-
Tom
-------------------------------------- --------------
Thomas A. Moreau，理学士，博士，MCSE，MCDBA
SQL Server MVP
专栏作家，SQL Server专业版
加拿大多伦多
www.pinpub.com
..
" Bill Bob" <无**** @ devdex.com>在消息中写道
新闻：8p *************** @ news.uswest.net ...
凭证表
7332429B-22BF-49B8-A5B6-00094FF9DD59 4 1
2001-07-04 19：54：00.000
26D65B5D-E389 -4E74-8605-000EC9F10575 14 1
2001-07-30 15：49：00.000
A40996E6-0581-48D9-91A9-000F1E0D0B46 14 1
2001-09-17 15：23：00.000
6EE61DEC-C157-4371-AAD3-0018B70B1A96 4 1
2001-09-15 21：48：00.000
267E5D45-F38D-4FB8-A2DF-00210BF9D037 4 1
2001-06-04 18：28：00.000
Transactions表
CBBD8EBE-55BA-4039-9C3B-0537FE348470
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB B 1 .0000
600.0000
836E2414-6E6B-4608-BF63-0A15DBD540DA
96C2D6F7-C3EF-4A92-9861-EA8E99E2D297 T 25 48.0000
0000
3221E16C-CB2E-487E-A875-4613BAFB40D2
4A181205-67B7-4B38-A9C8-D416ACE CA978 B 1 .0000
5000
1736F5A0-EBE3-4494-B075-52216E73E857
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB T 1 2.0000
0000
B475F07A-7012-4DC8-B0C9-7BE8A66493C8
4A181205-67B7-4B38-A9C8-D416ACECA978 B 232 .0000
$
Ledger表
1现金
2利润&损失A / c
3分行-1
4预留保证金1 /
5 DutiesTaxes-1
***通过开发人员指南 http://www.developersdex.com 发送***
Would it be possible to get these as INSERT VALUES statements?
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Bill Bob" <no****@devdex.com> wrote in message
news:8p***************@news.uswest.net...
Voucher Table
7332429B-22BF-49B8-A5B6-00094FF9DD59 4 1
2001-07-04 19:54:00.000
26D65B5D-E389-4E74-8605-000EC9F10575 14 1
2001-07-30 15:49:00.000
A40996E6-0581-48D9-91A9-000F1E0D0B46 14 1
2001-09-17 15:23:00.000
6EE61DEC-C157-4371-AAD3-0018B70B1A96 4 1
2001-09-15 21:48:00.000
267E5D45-F38D-4FB8-A2DF-00210BF9D037 4 1
2001-06-04 18:28:00.000
Transactions Table
CBBD8EBE-55BA-4039-9C3B-0537FE348470
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB B 1 .0000
600.0000
836E2414-6E6B-4608-BF63-0A15DBD540DA
96C2D6F7-C3EF-4A92-9861-EA8E99E2D297 T 25 48.0000
0000
3221E16C-CB2E-487E-A875-4613BAFB40D2
4A181205-67B7-4B38-A9C8-D416ACECA978 B 1 .0000
5000
1736F5A0-EBE3-4494-B075-52216E73E857
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB T 1 2.0000
0000
B475F07A-7012-4DC8-B0C9-7BE8A66493C8
4A181205-67B7-4B38-A9C8-D416ACECA978 B 232 .0000
5000
Ledger Table
1 Cash
2 Profit & Loss A/c
3 Branch-1
4 ReserveSurplus-1
5 DutiesTaxes-1
*** Sent via Developersdex http://www.developersdex.com ***
这篇关于艰难的Sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
