挑战：你能优化这个吗？ [英] Challenge: Can you optimize this?
问题描述
此代码试图查找具有RegJrnID的记录
在表格中不会出现多次。
我之所以这样做想要查找具有非重复RegJrnID的记录
值是创建反转值对于这些记录，反转
记录对于每个列都具有相同的值，但TaxableAmount
将包含负数。 （参见：下面的示例数据）。
/ *设置* /
CREATE TABLE t1（RegJrnID INTEGER，InvoiceDate VARCHAR（8） ，InvoiceNumber
VARCHAR（20），TaxableAmount DECIMAL（32,8））
/ *示例数据* /
INSERT INTO t1 VALUES（1，'''20060101''，''2321323''，100.00）
插入t1值（9，'''20060213''，'''2130009'' ，40.01）
插入t1值（3，'''20060101''，''9402293''，512.44）
插入t1值（1，''20060104 ''，'''2321323''， - 100.00）
INSERT INTO t1 VALUES（4，'''20060105''，''9302221''，612.12）
INSERT INTO t1 VALUES（5，'''20060105''，''0003235''，18.11）
INSERT INTO t1 VALUES（6，'''20060111''，''5953432''，2101.21）
INSERT INTO t1 VALUES（3，'''20060111''，''9402293''，-512.44）
插入t1值（7，''2006011 5''，''4234444''，44.52）
INSERT INTO t1 VALUES（8，'''20060115''，''0342222''，95.21）
INSERT INTO t1 VALUES（6，'''20060119''，''5953432''，-2101.21）
INSERT INTO t1 VALUES（2，'''20060101''，''5440033''，231.01）
/ *显示表中的内容 - 只是因为* /
SELECT * FROM t1 ORDER BY RegJrnID，InvoiceDate
/ *查询要反转的记录* /
SELECT *
FROM t1 a
/ *忽略已被撤消的记录* /
WHERE a.RegJrnID！= ALL
/ *该子选择查找反转记录（即那些有一个
重复的RegJrnID）* /
（
SELECT b.RegJrnID
从t1 b
GROUP BY b.RegJrnID
有计数（*）> 1
）
/ *用户选择标准附在此处* /
/ * AND InvoiceNumber> ='''5000000''和InvoiceNumber< ='''7500000''* /
/ *让结果看起来很漂亮（可选）* /
订购RegJrnID
/ *管家* /
DROP TABLE t1
This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.
The reason that I want to find records with non-duplicated RegJrnID
values is to create "reversal" records for these such that the reversal
record has identical values for every column except the TaxableAmount
which will contain a negative amount. (see: example data below).
/* Set up */
CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))
/* Example data */
INSERT INTO t1 VALUES (1, ''20060101'', ''2321323'', 100.00)
INSERT INTO t1 VALUES (9, ''20060213'', ''2130009'', 40.01)
INSERT INTO t1 VALUES (3, ''20060101'', ''9402293'', 512.44)
INSERT INTO t1 VALUES (1, ''20060104'', ''2321323'', -100.00)
INSERT INTO t1 VALUES (4, ''20060105'', ''9302221'', 612.12)
INSERT INTO t1 VALUES (5, ''20060105'', ''0003235'', 18.11)
INSERT INTO t1 VALUES (6, ''20060111'', ''5953432'', 2101.21)
INSERT INTO t1 VALUES (3, ''20060111'', ''9402293'', -512.44)
INSERT INTO t1 VALUES (7, ''20060115'', ''4234444'', 44.52)
INSERT INTO t1 VALUES (8, ''20060115'', ''0342222'', 95.21)
INSERT INTO t1 VALUES (6, ''20060119'', ''5953432'', -2101.21)
INSERT INTO t1 VALUES (2, ''20060101'', ''5440033'', 231.01)
/* Show what''s in the table - just because */
SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate
/* Query for records to reverse */
SELECT *
FROM t1 a
/* Ignore records that have already been reversed */
WHERE a.RegJrnID != ALL
/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */
(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)
/* User selection criteria are appended here */
/* AND InvoiceNumber >= ''5000000'' AND InvoiceNumber <= ''7500000'' */
/* Make the results look pretty (optional) */
ORDER BY RegJrnID
/* Housekeeping */
DROP TABLE t1
推荐答案
有很多方法可以实现这一点。我会从一些东西开始
这个（未经测试）：
选择pos.RegJrnID
来自（
select * from t1 where TaxableAmount> 0
）pos
left outer join
from（
select * from t1 where TaxableAmount< 0
）neg
on pos.RegJrnID = neg.RegJrnID
其中neg.RegJrnID为null
There are many ways to accomplish that. I would start with something
this (untested):
select pos.RegJrnID
from(
select * from t1 where TaxableAmount >0
) pos
left outer join
from(
select * from t1 where TaxableAmount <0
) neg
on pos.RegJrnID = neg.RegJrnID
where neg.RegJrnID is null
这是经过测试（并略微修改）的代码版本......
SELECT pos 。*
FROM
（
SELECT * FROM t1 WHERE TaxableAmount> 0
） pos
LEFT OUTER JOIN
（
SELECT * FROM t1 WHERE TaxableAmount< 0
）neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID为空
/ *使结果看起来漂亮（可选）* /
ORDER BY pos.RegJrnID
Here''s the tested (and slightly modified) version of your code...
SELECT pos.*
FROM
(
SELECT * FROM t1 WHERE TaxableAmount > 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID IS NULL
/* Make the results look pretty (optional) */
ORDER BY pos.RegJrnID
根据SQL Quer y分析器您的查询最好前往
头部，原始代表批次的43.43％，
原件代表批次的56.57％。
提高了13％！
谢谢！
According to the SQL Query analyzer your query is better going head to
head with the original representing 43.43% of the batch and the
original representing 56.57% of the batch.
A 13% improvement!
Thanks!
这篇关于挑战：你能优化这个吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
