katela Ответов: 2

Инструкция Sql для возврата списка частичных сборов

Пожалуйста, помогите мне, я застрял с этим SQL-оператором. Я новичок в SQL, я разрабатываю программное обеспечение для управления школой C#.

Это мой запрос Select:
SELECT distinct RTRIM(Students.StudentNumber),RTRIM(Students.StudentSurname),RTRIM(Students.StudentFirstNames),RTRIM(CourseFeePayment.PaymentDue) from Students,CourseFeePayment,CourseFeePayment_Join where Students.StudentNumber=CourseFeePayment.StudentNumber and CourseFeePayment.CourseFeePaymentID=CourseFeePayment_Join.C_PaymentID and CourseFeePayment.SchoolYear=@d1 and CourseFeePayment.Student_Class=@d2 and CourseFeePayment_Join.Month=@d3 and CourseFeePayment.PaymentDue > 0 order by 2

        cmd.Parameters.AddWithValue("@d1", cmbAcademicYear.Text);
        cmd.Parameters.AddWithValue("@d2", cmbClass.Text);
        cmd.Parameters.AddWithValue("@d3", cmbMonth.Text);
        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        dgw.Rows.Clear();
        while ((rdr.Read() == true))
         {
         dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4));
         }


Мне нужно это, чтобы вернуть список на строку каждого студента, который частично задолжал из этого конкретного класса только за этот конкретный месяц. Под частичным я подразумеваю, что если плата за класс составляет, например, 100 долларов за январь, то если студент ничего не заплатил (задолжал полную сумму), то его следует игнорировать и в этом случае перечислять только студентов, задолжавших от более чем 0 до менее чем 100.

Этот запрос хорошо работает только тогда, когда у меня есть только один студент и я возвращаю правильную оплату. Если у меня есть много студентов в том же месяце, он возвращает их, но с неправильной суммой оплаты. Если у меня есть студент, который заплатил в рассрочку (заплатил более одного раза), но не закончил, он возвращает каждый взнос одного и того же студента по другой строке с разной суммой платежа.

Что я уже пробовал:

Любая помощь будет оценена по достоинству. Вот мои таблицы:
Students Table:
StudentID (primary)
StudentNumber
StudentSurname
StudentFirstNames


CourseFeePayment Table:
CourseFeePaymentID (primary)
PaymentID
StudentNumber
SchoolYear
TotalFee  
PreviousDue
GrandTotal
TotalPaid
PaymentDue
Student_Class

CourseFeePayment_Join Table:
CourseFeePayment_J_ID
C_PaymentID
Fee
Month

2 Ответов

Рейтинг:
20

MadMyche

Во-первых, всегда полезно публиковать код, который имеет некоторое форматирование, что значительно облегчает людям чтение того, что вы пытаетесь сделать

SELECT distinct
	RTRIM(Students.StudentNumber)
,	RTRIM(Students.StudentSurname)
,	RTRIM(Students.StudentFirstNames)
,	RTRIM(CourseFeePayment.PaymentDue)
from 	Students
,	CourseFeePayment
,	CourseFeePayment_Join 
where	Students.StudentNumber			= CourseFeePayment.StudentNumber 
and	CourseFeePayment.CourseFeePaymentID	= CourseFeePayment_Join.C_PaymentID
and	CourseFeePayment.SchoolYear		= @d1
and	CourseFeePayment.Student_Class	= @d2
and	CourseFeePayment_Join.Month		= @d3
and	CourseFeePayment.PaymentDue		> 0
order by 2
Для моего следующего трюка.... Я собираюсь устранить эту проблему. RTrims так же ничего не делает для чисел, и текст должен был бы это сделать в момент вставки. Я также собираюсь использовать некоторые псевдонимы на именах таблиц, и я собираюсь писать в реальных соединениях, используя их.
Обратите внимание, что я могу получить весь список выбора аккуратно на одной строке
SELECT DISTINCT s.StudentNumber, s.StudentSurname, s.StudentFirstNames, p.PaymentDue

FROM	Students					s
INNER JOIN CourseFeePayment			p ON s.StudentNumber	= p.StudentNumber 
INNER JOIN CourseFeePayment_Join	j ON p.CourseFeePaymentID= j.C_PaymentID

WHERE	CourseFeePayment.SchoolYear		= @d1
AND		CourseFeePayment.Student_Class	= @d2
AND		CourseFeePayment_Join.Month		= @d3
AND		CourseFeePayment.PaymentDue		> 0

ORDER BY 2
Теперь я вижу, что с этим запросом возникает проблема.... Что это на самом деле будет делать, так это вытаскивать список студентов, которые должны были заплатить, а также сделали платеж в выбранном месяце.

Что нам действительно нужно сделать, так это совокупный суммы, которые выплачиваются студентом за выбранный период
SELECT	p.StudentNumber, SUM(p.TotalPaid)
FROM	CourseFeePayment		p
INNER JOIN CourseFeePayment_Join	j ON p.CourseFeePaymentID = j.C_PaymentID
WHERE	p.StudentClass	= @d2
AND		j.Month			= @d3
GROUP BY p.StudentNumber
Я действительно не хочу идти слишком далеко с этим; поскольку я не знаю никаких данных, которые у вас есть, и какие механизмы существуют для вставки платежных записей - это не тот дизайн, который я бы использовал. В зависимости от того, что все это происходит, вы скажете мне, если я собираюсь использовать эту агрегацию в качестве подзапроса, сохраните ее в #температура стол, или использовать его, как только через КТР

Я бы рекомендовал работать с этим в SSMS или каком-либо другом клиентском приложении SQL, так как это было бы гораздо более универсально и удобно.
Я бы также рекомендовал, чтобы, когда все будет сказано и сделано с этим запросом, вы просто преобразовали его в a Stored Procedure.

katela

Большое спасибо за вашу проницательность. Я постараюсь реализовать его таким образом и увижу результаты.
Спасибо

Maciej Los

Молодец!

Рейтинг:
2

rmpbklynnyc

вам нужно иметь sql-утверждение в execute.
вы закрываете соединение после чтения строк/вывода

напр. 

string tempValue;
                   string connStr = "server=localhost;user=root;database=mytestmydb;port=nnnn;password="+ppwd.ToString();
                   MySqlConnection conn = new MySqlConnection(connStr);
                   try
                   {


               DateTime dtnow = DateTime.Now;
               Console.WriteLine("Connecting to MySQL... time: "+ dtnow);
                       conn.Open();

                       string sql = "select * from books;";
                       MySqlCommand cmd = new MySqlCommand(sql, conn);
               MySqlDataReader mmreader = cmd.ExecuteReader();

               ////data rows
               while (mmreader.Read())
               {
                   tempValue = mmreader.GetString("author");

                   Console.WriteLine(tempValue.ToString());
               }






           }
                   catch (Exception ex)
                   {
                       Console.WriteLine(ex.ToString());
                   }

                   conn.Close();
                   Console.WriteLine("Done.");

katela

Спасибо