如何编写一个返回从当前SELECT返回的行的SQL SELECT [英] How to write an SQL SELECT that returns rows that do not get returned from my current SELECT
本文介绍了如何编写一个返回从当前SELECT返回的行的SQL SELECT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
string query =select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month(b.fee_of_month)= month(#+ DateTime .Now.ToShortDateString()+#)和year(b.fee_of_month)= year(#+ DateTime.Now.ToShortDateString()+#);
我使用上面的查询来检索那些已经支付了本月费用的学生的记录。此查询工作正常。
我想要一个新的查询来检索没有支付费用的学生的行。
如果我使用如下的查询,那么它将不会按预期返回行。请建议我可以用来完成任务的查询。谢谢。
string query =select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b .student_id月份(b.fee_of_month)<>月份(#+ DateTime.Now.ToShortDateString()+#)和年份(b.fee_of_month)=年份(#+ DateTime.Now.ToShortDateString()+ #);
解决方案
您的新查询将如下所示。抱歉,我没有时间构建测试用例来为您测试。这个概念是你从那些已经付费的student_ids和从add_student开始的那个student_ids中选择那些不在已经付费的那些中的student_ids。我希望这会对你有所帮助。
string query =select ad.student_id,ad.full_name来自add_student ad,其中ad.student_id不在(从结算b中选择b.student_id,其中月份(b.fee_of_month)=月份(#+ DateTime.Now.ToShortDateString()+#)和年份(b.fee_of_month)=年份(#+ DateTime.Now.ToShortDateString( )+#));
使用SELECT * FROM add_student ad inner join 结算b ad.student_id = b.student_id HAVING ( select ad.student_id,ad.full_name 来自 add_student ad inner 加入结算b ad.student_id = b.student_id 其中 month(b.fee_of_month)= month(# + DateTime.Now.ToShortDateString()+#)和年(b.fee_of_month)=年(# + DateTime.Now.ToShortDateString()+跨度>#))
string query = "select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#)";
I am using above query to retrieve record of those students that have paid fees for this month. This query is working fine.
I want a new query to retrieve rows for those students who have not paid their fees.
If I use query like below, then it will not return rows as expected. Please suggest a query that I can use to achieve my task. Thank you.
string query = "select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) <> month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#)";
解决方案
Your new query will look something like this. Sorry, I do not have time to build a test case to test it for you. The concept is that you select from billing those student_ids that have paid and from add_student you select those student_ids that are not in the set of those that have paid. I hope this helps you.
string query = "select ad.student_id,ad.full_name from add_student ad where ad.student_id not in (select b.student_id from billing b where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#))";
useSELECT * FROM add_student ad inner join billing b on ad.student_id = b.student_id HAVING (select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#))
这篇关于如何编写一个返回从当前SELECT返回的行的SQL SELECT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文