需要帮助编写查询
下图已上传,以显示我正在尝试做什么以及我想要从中得到什么
任何人都可以帮我编写查询以获得我想要的结果 请检查以下
SELECT *
FROM KPT
WHERE PROPERTY_ID IN (SELECT PROPERTY_ID
FROM khata_header
WHERE DIV_ID = 3
and RECORD_STATUS = 0)
and CHALLAN_NO > 42646
内容 上面是我编写的查询,我得到了以下结果集
ID CHALLAN_NO PROPERTY_ID SITE_NO TOTAL_AMOUNT
----- ------------- -------------- ------------------- ---------------
1242 42757 3103010141 296 595
1243 63743 3204190257 483 594
1244 63743 3204190257 483 594
1334 43395 3217010223 1088 576
1421 524210 3320050416 (null) (null)
1422 524210 3320050416 (null) (null)
1560 564355 3320021408 (null) (null)
1870 516292 3320040420 (null) (null)
1940 68357 3217100104 139 1153
1941 68357 3217100104 139 1153
2002 56256 3320100733 511 4430
2003 56256 3320100733 511 4430
2004 66488 3217040869 293 3094
2005 66488 3217040869 293 3094
2016 64571 3217040374 (null) (null)
2036 523122 3320020352 (null) (null)
2039 65682 3217040021 273 919
在我的结果集中,我得到了重复的 PropertyId,因为有多个条目,
- 我如何知道重复了多少个条目
- 那些属性是什么其中 ID 重复次数超过 2 次。
关于表的小背景是
- PROPERTY_ID 是 KPT 中的 FK
- PROPERTY_ID 是 KH 中的 PK
我正在编写一个子查询来获取结果,所以我陷入困境,我不知道如何获取结果请帮助
The following image have been uploaded to show what I am trying to do and what I wanted out of it
Can any one help me write the Query to get the results what I want
Please check the following
SELECT *
FROM KPT
WHERE PROPERTY_ID IN (SELECT PROPERTY_ID
FROM khata_header
WHERE DIV_ID = 3
and RECORD_STATUS = 0)
and CHALLAN_NO > 42646
The above is the query I have written and I have got the following result set
ID CHALLAN_NO PROPERTY_ID SITE_NO TOTAL_AMOUNT
----- ------------- -------------- ------------------- ---------------
1242 42757 3103010141 296 595
1243 63743 3204190257 483 594
1244 63743 3204190257 483 594
1334 43395 3217010223 1088 576
1421 524210 3320050416 (null) (null)
1422 524210 3320050416 (null) (null)
1560 564355 3320021408 (null) (null)
1870 516292 3320040420 (null) (null)
1940 68357 3217100104 139 1153
1941 68357 3217100104 139 1153
2002 56256 3320100733 511 4430
2003 56256 3320100733 511 4430
2004 66488 3217040869 293 3094
2005 66488 3217040869 293 3094
2016 64571 3217040374 (null) (null)
2036 523122 3320020352 (null) (null)
2039 65682 3217040021 273 919
In my resultset, I am getting the PropertyId repeated, since there are multilple entries,
- How Can I know How many have been repeated
- What are those Property Id which have repeated more than 2 times.
Little Back ground about the tables are
- PROPERTY_ID is the FK in the KPT
- PROPERTY_ID is the PK in KH
I am writing a subquery to get the Result, so I am stuck I dont know how to get my results Please help
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
这会让你更亲近吗?
Does this get you any closer?
要确定哪些属性 id 已重复,可以使用以下未经测试的查询。
要确定哪些属性 ID 已重复两次以上,请将以下
HAVING
子句添加到上述查询的末尾。To determine which property ids have been repeated, the following untested query should work.
To determine which property ids have been repeated more than two times, add the following
HAVING
clause to the end of the above query.也许你可以尝试这个
PROPERTY_ID 重复计数可以通过 count() 和 group by 来实现,要获得 PROPERTY_ID 重复超过 2 次,你可以在临时表中创建第一个选择,并在临时表上创建 2 个选择,最后删除临时表。
--create temp table
-- 问题 1:
--and
union all
-- 问题 2:
希望这能回答您的问题。
祝你好运
Maybe you can try this
The PROPERTY_ID repeat count can be achieved by an count() and a group by, to obtain the PROPERTY_ID's repeated more than 2 times you can create the first select into a temp table and create 2 select's on the temp table, at the end drop the temp table.
--create temp table
-- question 1:
--and
union all
-- question 2:
Hopefully this answers your question.
Good luck