合并 2 个表中的数据 [英] Merging data from 2 tables
问题描述
免责声明:这是一个测试/虚拟/假数据库
DISCLAIMER : THIS IS A TEST/DUMMY/FAKE DATABASE
大家好，我有一个问题，下面是 2 个表结构.当我使用
Hi guys, i have a problem, below are the 2 table structures. When i use
SELECT CONVERT(char(80), InvDate,3) AS InvDate,InvoiceNo,EmployerCode,TaxAmount + SubTotal AS Amount,'' AS Payment FROM dbo.Invoice;
我希望在列中添加患者姓名，其中将标记为发票编号.所以我的意思是，当执行查询时，它应该向我显示与发票编号一起标记的患者详细信息.但是在两个表结构中都没有链接.我能想到的唯一链接MedicalRecordID".我尝试使用 UNION 函数没有给我想要的输出.有什么帮助吗?
I wish to add in a column patients name where by it will be tagged to invoice number. So what i mean is that, when the query is executed, it should show me the patientdetails tagged together with invoice number. But in the both table structures there are no links. The only linkage i can think of "MedicalRecordID". I'm tried using UNION function didnt give me the desired output. Any help?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace MedicalDataExporter
{
public partial class frmSales : Form
{
public frmSales()
{
InitializeComponent();
}
private void dtpFrom_ValueChanged(object sender, EventArgs e)
{
}
private void btnExtract_Click(object sender, EventArgs e)
{
SqlConnection objConn = new SqlConnection("Data Source=test;Initial Catalog=test;Persist Security Info=True;User ID=test;Password=test");
System.Data.SqlClient.SqlConnection(conStr);
objConn.Open();
SqlCommand objCmd = new SqlCommand("SELECT CONVERT(char(80), InvDate,3) AS InvDate,InvoiceNo,EmployerCode,TaxAmount + SubTotal AS Amount,'' AS Payment FROM Invoice WHERE (InvDate >= CONVERT(datetime, '"+dtpFrom.Text +"', 105 )) AND (InvDate <= CONVERT(datetime, '"+dtpTo.Text+"', 105))", objConn);
SqlDataReader objReader;
objReader = objCmd.ExecuteReader();
System.IO.FileStream fs = new System.IO.FileStream("C:\\CMSExportedData\\Sales-" + DateTime.Now.ToString("dd-MM-yyyy") + ".txt", System.IO.FileMode.Create);
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
int count = 0;
while (objReader.Read())
{
for (int i = 0; i < 5; i++)
{
if (!objReader.IsDBNull(i))
{
string s;
s = objReader.GetDataTypeName(i);
//MessageBox.Show(s);
if (objReader.GetDataTypeName(i) == "char")
{
sw.Write(objReader.GetString(i));
}
else if (objReader.GetDataTypeName(i) == "money")
{
sw.Write(objReader.GetSqlMoney(i).ToString());
}
else if (objReader.GetDataTypeName(i) == "nvarchar")
{
sw.Write(objReader.GetString(i));
}
}
if (i < 4)
{
sw.Write("\t");
}
}
count = count + 1;
sw.WriteLine();
}
sw.Flush();
fs.Close();
objReader.Close();
objConn.Close();
MessageBox.Show(count + " records exported successfully.");
this.Close();
}
private void groupBox1_Enter(object sender, EventArgs e)
{
}
private void dtpTo_ValueChanged(object sender, EventArgs e)
{
}
private void frmSales_Load(object sender, EventArgs e)
{
}
}
}
表结构如下:
这是第二个表结构:
推荐答案
要跨多个表查询数据，您需要加入表格.我不是 100% 清楚你的两个表之间的关系，但如果 MedicalRecordID 是正确的关系，那么你的查询应该是这样的:
To query data across multiple tables, you want to join the tables. I'm not 100% clear on the relationship between your two tables, but if MedicalRecordID is the correct relationship, then your query should look something like this:
SELECT
CONVERT(char(80), i.InvDate,3) AS InvDate,
i.InvoiceNo,
i.EmployerCode,
i.TaxAmount + i.SubTotal AS Amount,
'' AS Payment,
pd.LastName,
pd.GivenName
FROM
dbo.Invoice i
INNER JOIN dbo.PatientDetails pd ON (pd.MedicalRecordID = i.MedicalRecordID)
;
如果表之间存在一对一关系，并且每张发票总是有一个 PatientDetails 记录，则此方法有效.如果 PatientDetails 是可选的，则使用 LEFT JOIN 而不是 INNER JOIN.
This works if there is a one-to-one relationship between tables, and if there is always a PatientDetails record for each invoice. If PatientDetails is optional, then use LEFT JOIN instead of INNER JOIN.
编辑(回复评论):
我敢打赌，您的 WHERE 子句中的 DateTime 转换没有按您预期的方式工作.假设 dtpFrom 和 dtpTo 是 DatePicker 控件，您可能希望使用 SelectedDate 属性而不是 文字.此外，我强烈建议您在查询中使用参数而不是连接字符串.您的代码将更简洁，并且您将避免SQL 注入.这是一个简单的例子:
I'm betting that the DateTime conversion in your WHERE clause is not working the way you expect. Assuming that dtpFrom and dtpTo are DatePicker controls, you probably want to use the SelectedDate property instead of Text. Also, I would highly recommend using parameters in your queries rather than concatenating strings. Your code will be cleaner, and you'll avoid SQL injection. Here's a quick example:
using (SqlConnection connection = new SqlConnection( ... ))
{
connection.Open();
string sql = @"
SELECT
CONVERT(char(80), i.InvDate,3) AS InvDate,
i.InvoiceNo,
i.EmployerCode,
i.TaxAmount + i.SubTotal AS Amount,
'' AS Payment,
pd.GivenName
FROM
dbo.Invoice i
LEFT JOIN dbo.PatientDetails pd ON (pd.MedicalRecordID = i.MedicalRecordID)
WHERE
InvDate >= @fromDate AND InvDate <= @toDate";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@fromDate", dtpFrom.SelectedDate);
cmd.Parameters.AddWithValue("@toDate", dtpTo.SelectedDate);
using (SqlDataReader reader = cmd.ExecuteReader())
{
// do stuff with results
}
}
这篇关于合并 2 个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
