sql union all, 탭인덱스

sql문 써보기

union all

SELECT z.useddate, MAX(`수입`) `수입`, MAX(`지출`)`지출`
FROM (

SELECT SUBSTRING(dc.useddate,6,2) useddate, SUM(money) `수입`, 0 `지출` FROM dc_account dc
WHERE (dc.useddate BETWEEN '2022-01-01' AND '2022-02-28') AND flagYN='Y' AND accacount='수입'
GROUP BY dc.useddate

UNION ALL

SELECT SUBSTRING(dc.useddate,6,2) useddate, 0 `수입`, SUM(money) `지출` FROM dc_account dc
WHERE (dc.useddate BETWEEN '2022-01-01' AND '2022-02-28') AND flagYN='Y' AND accacount='지출'
GROUP BY dc.usedDate) z GROUP BY z.useddate ORDER BY z.useddate ASC;

 

SELECT useddate, MAX(`수입`)`수입`,MAX(`지출`)`지출`
FROM(
SELECT useddate, SUM(money)`수입`,0 `지출` FROM dc_account
WHERE (useddate BETWEEN '2022-01-01' AND '2022-02-28') AND flagYN = 'Y' AND accacount='수입'
GROUP BY useddate

UNION ALL

SELECT useddate, 0 `수입`, SUM(money) `지출` FROM dc_account
WHERE (useddate BETWEEN '2022-01-01' AND '2022-02-28') AND flagYN='Y' AND accacount='지출'
GROUP BY useddate)z GROUP BY useddate ORDER BY useddate ASC;

 

 

 

 

탭 인덱스가 첫번째일때 차트에 들어갈 쿼리를 선언하고 dailyChart함수에 넣기

 

if (tabControl1.SelectedIndex == 0)
{
string DayIncomeChartQuery = "select accAcount, SUBJECT, sum(money)  money FROM dc_account WHERE usedDate = '" +
dateTimePicker4.Text + "'"
+ "AND accAcount = '수입' AND flagYN ='Y' GROUP BY SUBJECT ORDER BY useddate asc;";
string DayOutcomeChartQuery = "select accAcount, SUBJECT, sum(money)  money FROM dc_account WHERE usedDate = '" +
dateTimePicker4.Text + "'"
+ "AND accAcount = '지출' AND flagYN ='Y' GROUP BY SUBJECT ORDER BY useddate asc;";
string DayBarChartQuery = "select useddate, accAcount, sum(money)  money FROM dc_account WHERE usedDate = '" +
dateTimePicker4.Text + "'"
+ "AND flagYN ='Y' GROUP BY useddate,accacount ORDER BY useddate asc;";
string DayunionQuery = "SELECT useddate, MAX(`수입`) income, MAX(`지출`) outcome FROM(select useddate, sum(money) `수입` , 0 `지출`  FROM dc_account WHERE(usedDate = '" + dateTimePicker4.Text
+ "') AND flagYN = 'Y'  AND accacount = '수입' GROUP BY useddate UNION all select useddate, 0 `수입`, sum(money) `지출`  FROM dc_account WHERE(usedDate = '"
+ dateTimePicker4.Text + "') AND flagYN = 'Y'  AND accacount = '지출' GROUP BY useddate) Z group by useddate ORDER BY useddate ASC; ";


dailyChart(DayIncomeChartQuery, DayOutcomeChartQuery, DayunionQuery, chart1, chart3, chart2);
}

탭 인덱스가 두번째일때 차트에 들어갈 쿼리를 선언하고 dailyChart함수에 넣기

	else if (tabControl1.SelectedIndex == 1)
				{
	//기간일자
	string DateIncomeChartQuery = "select accAcount, SUBJECT, sum(money)  money FROM dc_account WHERE  (usedDate BETWEEN '" +
					dateTimePicker3.Text + "' AND '" + dateTimePicker5.Text + "')"
					+ "AND accAcount = '수입' AND flagYN ='Y' GROUP BY SUBJECT ORDER BY useddate asc;";

					string DateOutcomeChartQuery = "select accAcount, SUBJECT, sum(money)  money FROM dc_account WHERE  (usedDate BETWEEN '" +
					dateTimePicker3.Text + "' AND '" + dateTimePicker5.Text + "')"
					+ "AND accAcount = '지출' AND flagYN ='Y' GROUP BY SUBJECT ORDER BY useddate asc;";

					string DateBarChartQuery = "select useddate, accAcount, sum(money)  money FROM dc_account WHERE (usedDate BETWEEN '" +
					dateTimePicker3.Text + "' AND '" + dateTimePicker5.Text + "')"
							+ " AND flagYN ='Y' GROUP BY useddate,accacount ORDER BY useddate asc;";

					string unionQuery = "SELECT useddate, MAX(`수입`) income, MAX(`지출`) outcome FROM(select useddate, sum(money) `수입` , 0 `지출`  FROM dc_account WHERE(usedDate BETWEEN '" + dateTimePicker3.Text
						+ "' AND '" + dateTimePicker5.Text + "') AND flagYN = 'Y'  AND accacount = '수입' GROUP BY useddate UNION all select useddate, 0 `수입`, sum(money) `지출`  FROM dc_account WHERE(usedDate BETWEEN " +
						"'" + dateTimePicker3.Text + "' AND '" + dateTimePicker5.Text + "') AND flagYN = 'Y'  AND accacount = '지출' GROUP BY useddate) Z group by useddate ORDER BY useddate ASC; ";

					dailyChart(DateIncomeChartQuery, DateOutcomeChartQuery, unionQuery, chart4, chart5, chart6);
				}

탭 인덱스가 세번째일때 차트에 들어갈 쿼리를 선언하고 dailyChart함수에 넣기

	else if (tabControl1.SelectedIndex == 2)
				{
					string keyYear2 = comboBoxYear2.Text;
					string year = "";
					if (keyYear2 == "2022") year = "2022";
					else if (keyYear2 == "2020") year = "2020";
					else if (keyYear2 == "2021") year = "2021";
					else if (keyYear2 == "2023") year = "2023";
					else if (keyYear2 == "2024") year = "2024";
					else if (keyYear2 == "2025") year = "2025";
					//월간
					string MonthIncomeChartQuery = "select accAcount, SUBJECT, sum(cast(replace(money,',','') AS INT))  money FROM dc_account WHERE usedDate like '"
					+ "%" + year + "%' "
					+ "AND accAcount = '수입' AND flagYN ='Y' GROUP BY SUBJECT ORDER BY useddate asc;";
					string MonthOutcomeChartQuery = "select accAcount, SUBJECT, sum(cast(replace(money,',','') AS INT))  money FROM dc_account WHERE usedDate like '"
						+ "%" + year + "%' "
						+ "AND accAcount = '지출' AND flagYN ='Y' GROUP BY SUBJECT ORDER BY useddate asc;";
					string MonthBarChartQuery = "select useddate, accAcount, sum(money)  money FROM dc_account WHERE usedDate like '"
							+ "%" + year + "%' "
							+ "AND flagYN ='Y' GROUP BY useddate,accacount ORDER BY useddate asc;";
					string MBUnionQuery = "SELECT z.useddate, MAX(`수입`) income, MAX(`지출`) outcome FROM( SELECT SUBSTRING(dc.useddate, 6, 2) useddate, sum(money) `수입` , 0 `지출`  FROM dc_account dc WHERE(dc.usedDate like '%" + year + "%') AND flagYN = 'Y'  AND accacount = '수입' GROUP BY dc.useddate UNION all select substring(dc.useddate, 6, 2) useddate, 0 `수입`, sum(money) `지출`  FROM dc_account dc WHERE(dc.usedDate like '%" + year + "%') AND flagYN = 'Y'  AND accacount = '지출' GROUP BY dc.useddate) Z group BY z.useddate ORDER BY z.useddate ASC; ";
					dailyChart(MonthIncomeChartQuery, MonthOutcomeChartQuery, MBUnionQuery, chart7, chart8, chart9);
				}

dailyChart()

private void dailyChart(string incomeQuery, string outcomeQuery, string barQuery, Chart pieIncomeChart, Chart pieOutcomeChart, Chart barChart)
		{
			string strConn = "datasource=127.0.0.1;port=3306;database=dawoon;username=root;password=ekdnsel;Charset=utf8";
			try
			{
				pieIncomeChart.Series[0]["PieStartAngle"] = "270";
				pieIncomeChart.Series[0].IsVisibleInLegend = true;
				pieIncomeChart.Legends[0].Docking = System.Windows.Forms.DataVisualization.Charting.Docking.Bottom;
				pieIncomeChart.Legends[0].Alignment = StringAlignment.Center;
				pieIncomeChart.Series[0].BorderWidth = 1;
				pieIncomeChart.Series[0].BorderColor = Color.Black;
				pieIncomeChart.Series[0].Label = "#VALX \n(#PERCENT{P1})";
				pieIncomeChart.Series[0].LegendText = "#VALX(#PERCENT{P1})\n금액: #VALY{N0}원";
				pieIncomeChart.DataManipulator.Sort(System.Windows.Forms.DataVisualization.Charting.PointSortOrder.Descending,
				pieIncomeChart.Series[0]);
				pieIncomeChart.Series[0]["CollectedSliceExploded"] = "true";
			  pieIncomeChart.Series[0]["CollectedThreshold"] = "30";
				pieIncomeChart.Series[0]["CollectedThresholdUsePercent"] = "false";
				pieIncomeChart.Series[0]["CollectedLabel"] = "기타";
				pieIncomeChart.Series[0]["CollectedLegendText"] = "기타(10%미만)";

				pieOutcomeChart.Series[0]["PieStartAngle"] = "270";
				pieOutcomeChart.Series[0].IsVisibleInLegend = true;
				pieOutcomeChart.Legends[0].Docking = System.Windows.Forms.DataVisualization.Charting.Docking.Bottom;
				pieOutcomeChart.Legends[0].Alignment = StringAlignment.Center;
				pieOutcomeChart.Series[0]["PieLabelStyle"] = "OutSide";
				pieOutcomeChart.Series[0]["PieLineColor"] = "Blue";
				pieOutcomeChart.Series[0].BorderWidth = 1;
				pieOutcomeChart.Series[0].BorderColor = Color.Black;
				pieOutcomeChart.Series[0].Label = "#VALX\n(#PERCENT{P0})";
				pieOutcomeChart.Series[0].LegendText = "#VALX(#PERCENT{P0})\n금액: #VALY{N0}원";
				pieOutcomeChart.DataManipulator.Sort(System.Windows.Forms.DataVisualization.Charting.PointSortOrder.Descending,
				pieOutcomeChart.Series[0]);
				pieOutcomeChart.Series[0]["CollectedSliceExploded"] = "true";
				pieOutcomeChart.Series[0]["CollectedThreshold"] = "10";
				pieOutcomeChart.Series[0]["CollectedThresholdUsePercent"] = "false";
				pieOutcomeChart.Series[0]["CollectedLabel"] = "기타";
				pieOutcomeChart.Series[0]["CollectedLegendText"] = "기타(10%미만)";
				using (MySqlConnection conn = new MySqlConnection(strConn)) // 연결클래스 conn 생성
			{
				conn.Open(); // conn 연결
				MySqlCommand cmd = new MySqlCommand(incomeQuery, conn); //명령클래스 cmd 생성
				DataSet ds = new DataSet(); //데이터셋 ds 생성
				MySqlDataAdapter sa = new MySqlDataAdapter(cmd); //cmd인자를 받은 데이터어뎁터 sa 생성
				sa.Fill(ds); //sa 데이터어뎁터 변수를 ds데이터셋에 채워라
										 // DataTable 객체를 DataSource에 지정하고,
										 // X,Y축 컬럼을 XValueMember와 YValueMembers에 지정
				pieIncomeChart.DataSource = ds.Tables[0]; //chart6.데이터소스는 ds,Tables의 첫번째로 지정
				pieIncomeChart.Series[0].XValueMember = "subject"; // x축 컬럼 subject필드
				pieIncomeChart.Series[0].YValueMembers = "money"; // y축 컬럼 money필드

				pieIncomeChart.DataBind(); //chart2 데이터바인딩
				pieIncomeChart.Series[0].ChartType = SeriesChartType.Pie;
		
										 //가계부.usedDate(between '" + dateTimePicker2.Value.ToString() + "' and '" + dateTimePicker1.Value.ToString() + "') and " +
			}
			using (MySqlConnection conn = new MySqlConnection(strConn)) // 연결클래스 conn 생성
			{
		
				conn.Open(); // conn 연결
				MySqlCommand cmd = new MySqlCommand(outcomeQuery, conn); //명령클래스 cmd 생성
				DataSet ds = new DataSet(); //데이터셋 ds 생성
				MySqlDataAdapter sa = new MySqlDataAdapter(cmd); //cmd인자를 받은 데이터어뎁터 sa 생성
				sa.Fill(ds); //sa 데이터어뎁터 변수를 ds데이터셋에 채워라
										 // DataTable 객체를 DataSource에 지정하고,
										 // X,Y축 컬럼을 XValueMember와 YValueMembers에 지정
				pieOutcomeChart.DataSource = ds.Tables[0]; //chart6.데이터소스는 ds,Tables의 첫번째로 지정
				pieOutcomeChart.Series[0].XValueMember = "subject"; // x축 컬럼 subject필드
				pieOutcomeChart.Series[0].YValueMembers = "money"; // y축 컬럼 money필드

				pieOutcomeChart.DataBind(); //chart2 데이터바인딩
				pieOutcomeChart.Series[0].ChartType = SeriesChartType.Pie;
			}

			using (MySqlConnection conn = new MySqlConnection(strConn)) // 연결클래스 conn 생성
			{
				barChart.Series.Clear();
			
				barChart.Series.Add("수입");
				barChart.Series.Add("지출");
				conn.Open(); // conn 연결
				MySqlCommand cmd = new MySqlCommand(barQuery, conn); //명령클래스 cmd 생성
				DataTable dTable = new DataTable();
				MySqlDataReader rdr = cmd.ExecuteReader();
					string[] date = new string[100];
					int[] income = new int[100];
					int[] outcome = new int[100];

					
					while (rdr.Read())
				{
						string lengths = rdr["usedDate"].ToString();
					//	MessageBox.Show(lengths.Length.ToString());
        if(tabControl1.SelectedIndex ==2)
					barChart.Series[0].Points.AddXY(rdr["usedDate"].ToString(), rdr["income"].ToString());
				else
					barChart.Series[0].Points.AddXY(rdr["usedDate"].ToString().Substring(0, 10), rdr["income"].ToString());
					barChart.Series[0].IsVisibleInLegend = true;
					barChart.Series[0].Label = "[수입] #VALY{N0}원";

					if (tabControl1.SelectedIndex == 2)
							barChart.Series[1].Points.AddXY(rdr["usedDate"].ToString(), rdr["income"].ToString());
					else
						barChart.Series[1].Points.AddXY(rdr["usedDate"].ToString().Substring(0,10), rdr["outcome"].ToString());
						barChart.Series[1].IsVisibleInLegend = true;
						barChart.Series[1].Label = "[지출] #VALY{N0}원";
				
				}

				rdr.Close();

				//	MySqlDataAdapter sa = new MySqlDataAdapter(cmd); //cmd인자를 받은 데이터어뎁터 sa 생성
				//		sa.Fill(dTable);
				//	barChart.DataSource = dTable; //chart6.데이터소스는 ds,Tables의 첫번째로 지정
				//		barChart.DataBindCrossTable(dTable.AsEnumerable(), "accAcount", "usedDate", "money", "");
			}
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}


		}