sql合并重复的字段

一句话搞定,这只是一个简单的交叉查询问题:

1

方法1:

ACCESS有向导的,在查询视图里找到上面[插入]菜单,选择交叉查询,交叉查询要求,必须有值进行计算,必须有列进行分类,必须有行进行分组,但N个组只能交叉1列数据,所以你就该把

数量

的合计

作为值

产品型号

分组

作为列用来分类

其他字段

分组

作为行用来分组

明白没,如果还没明白就用代码

2

方法2用代码:(看仔细我的代码怎么写的格式不能错)

TRANSFORM

Sum(数目)

as

数量合计

Select

订单号,

姓名,

商品单价,

配送方式,

数量合计单价

as

商品金额,

IIF(配送方式=韵达快递,12,0)

as

配送金额,

配送金额+商品金额

as

金额合计

From

Order

Group

By

订单号,

姓名,

商品单价,

配送方式,

数量合计单价,

iif(配送方式='韵达快递',12,0),

配送金额+商品金额

PIVOT

Order产品型号;

看懂没,直接复制进去检查一下符号用一下

交叉查询语句是这么写的:

TransForm

统计函数体

Select

分组行的字段名字段名

From

表名

Group

By

分组的字段名

Pivot

作为列的字段名;

统计函数体就是交叉计算的结果,他叫

值,分组行的字段名,是作为行的,他的作用就是体现每条记录的详细特征,Group

By关键字是针对分组汇总的结束语,Pivot是将行转换成列,记住,只能计算一个列,

在SQL中和在ACCESS语句都可以实现,下面这些哥们没试过,不代表没办法实现,在数据库中这种算法很普遍,这叫做交叉查询

如果数据源中只包含的链接， 要实现该功能，可通过非绑定列的方式来实现。具体实现方法如下： 1 创建了一个非绑定列并设置其相应的属性，属性设置如下： FieldName设为 Image (该字段名必须是唯一的) UnboundType设为 UnboundColumnTypeObject ColumnEdit设为RepositoryItemPictureEdit类的实例(该 *** 作PictureEdit 为该列的内置编辑器) 2 处理View的CustomUnboundColumnData事件，用于为非绑定列填充数据。在该事件中需加载，将其存放在一个hashtable中，然后再将其提供给对应的单元格。 关键代码： //获取文件路径 string GetFileName(string color) { if(color == null || color == stringEmpty) return stringEmpty; return color + "jpg"; } //处理CustomUnboundColumnData事件，为非绑定列填充数据 private void gridView1_CustomUnboundColumnData(object sender, DevExpressXtraGridViewsBaseCustomColumnDataEventArgs e) { if(eColumnFieldName == "Image" && eIsGetData) { GridView view = sender as GridView; string colorName = (string)((DataRowView)eRow)["Color"]; string fileName = GetFileName(colorName)ToLower(); if(!ImagesContainsKey(fileName)) { Image img = null; try { string filePath = DevExpressUtilsFilesHelperFindingFileName(ApplicationStartupPath, ImageDir + fileName, false); img = ImageFromFile(filePath); } catch { } ImagesAdd(fileName, img); } eValue = Images[fileName]; } } 点击下载示例 本站文章除注明转载外，均为本站原创或翻译

事例数据库如下： 代码如下： C# using DevExpressLookAndFeel; using DevExpressXtraPivotGrid; using SystemDataOleDb; // Create a connection object OleDbConnection connection = new OleDbConnection("Provider=MicrosoftJetOLEDB40;Data Source=C:\\DB\\NWINDMDB"); // Create a data adapter OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT FROM SalesPerson", connection); // Create and fill a dataset DataSet sourceDataSet = new DataSet(); adapterFill(sourceDataSet, "SalesPerson"); // Assign the data source to the XtraPivotGrid control pivotGridControl1DataSource = sourceDataSetTables["SalesPerson"]; // Create a row PivotGridControl field bound to the Country datasource field PivotGridField fieldCountry = new PivotGridField("Country", PivotAreaRowArea); // Create a row PivotGridControl field bound to the Sales Person datasource field PivotGridField fieldCustomer = new PivotGridField("Sales Person", PivotAreaRowArea); fieldCustomerCaption = "Customer"; // Create a column PivotGridControl field bound to the OrderDate datasource field PivotGridField fieldYear = new PivotGridField("OrderDate", PivotAreaColumnArea); fieldYearCaption = "Year"; // Group field values by years fieldYearGroupInterval = PivotGroupIntervalDateYear; // Create a column PivotGridControl field bound to the CategoryName datasource field PivotGridField fieldCategoryName = new PivotGridField("CategoryName", PivotAreaColumnArea); fieldCategoryNameCaption = "Product Category"; // Create a filter PivotGridControl field bound to the ProductName datasource field PivotGridField fieldProductName = new PivotGridField("ProductName", PivotAreaFilterArea); fieldProductNameCaption = "Product Name"; // Create a data PivotGridControl field bound to the 'Extended Price' datasource field PivotGridField fieldExtendedPrice = new PivotGridField("Extended Price", PivotAreaDataArea); fieldExtendedPriceCellFormatFormatType = DevExpressUtilsFormatTypeNumeric; // Specify the formatting setting to format summary values as integer currency amount fieldExtendedPriceCellFormatFormatString = "c0"; // Add the fields to the control's field collection pivotGridControl1FieldsAddRange(new PivotGridField[] {fieldCountry, fieldCustomer, fieldCategoryName, fieldProductName, fieldYear, fieldExtendedPrice}); // Arrange the row fields within the Row Header Area fieldCountryAreaIndex = 0; fieldCustomerAreaIndex = 1; // Arrange the column fields within the Column Header Area fieldCategoryNameAreaIndex = 0; fieldYearAreaIndex = 1; // Customize the control's look-and-feel via the Default LookAndFeel object UserLookAndFeelDefaultUseWindowsXPTheme = false; UserLookAndFeelDefaultStyle = LookAndFeelStyleSkin; UserLookAndFeelDefaultSkinName = "Money Twins"; VB Imports DevExpressLookAndFeel Imports DevExpressXtraPivotGrid Imports SystemDataOleDb ' Create a connection object Dim connection As OleDbConnection = New OleDbConnection("Provider=MicrosoftJetOLEDB40;Data Source=C:\\DB\\NWINDMDB") ' Create a data adapter Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT FROM SalesPerson", connection) ' Create and fill a dataset Dim sourceDataSet As DataSet = New DataSet adapterFill(sourceDataSet, "SalesPerson") ' Assign the data source to the XtraPivotGrid control PivotGridControl1DataSource = sourceDataSetTables("SalesPerson") ' Create a row PivotGridControl field bound to the Country datasource field Dim fieldCountry As PivotGridField = New PivotGridField("Country", PivotAreaRowArea) ' Create a row PivotGridControl field bound to the Sales Person datasource field Dim fieldCustomer As PivotGridField = New PivotGridField("Sales Person", PivotAreaRowArea) fieldCustomerCaption = "Customer" ' Create a column PivotGridControl field bound to the OrderDate datasource field Dim fieldYear As PivotGridField = New PivotGridField("OrderDate", PivotAreaColumnArea) fieldYearCaption = "Year" ' Group field values by years fieldYearGroupInterval = PivotGroupIntervalDateYear ' Create a column PivotGridControl field bound to the CategoryName datasource field Dim fieldCategoryName As PivotGridField = New PivotGridField("CategoryName", PivotAreaColumnArea) fieldCategoryNameCaption = "Product Category" ' Create a filter PivotGridControl field bound to the ProductName datasource field Dim fieldProductName As PivotGridField = New PivotGridField("ProductName", PivotAreaFilterArea) fieldProductNameCaption = "Product Name" ' Create a data PivotGridControl field bound to the 'Extended Price' datasource field Dim fieldExtendedPrice As PivotGridField = New PivotGridField("Extended Price", PivotAreaDataArea) fieldExtendedPriceCellFormatFormatType = DevExpressUtilsFormatTypeNumeric ' Specify the formatting setting to format summary values as integer currency amount fieldExtendedPriceCellFormatFormatString = "c0" ' Add the fields to the control's field collection PivotGridControl1FieldsAddRange(New PivotGridField() {fieldCountry, fieldCustomer, _ fieldCategoryName, fieldProductName, fieldYear, fieldExtendedPrice}) ' Arrange the row fields within the Row Header Area fieldCountryAreaIndex = 0 fieldCustomerAreaIndex = 1 ' Arrange the column fields within the Column Header Area fieldCategoryNameAreaIndex = 0 fieldYearAreaIndex = 1 ' Customize the control's look-and-feel via the Default LookAndFeel object UserLookAndFeelDefaultUseWindowsXPTheme = False UserLookAndFeelDefaultStyle = LookAndFeelStyleSkin UserLookAndFeelDefaultSkinName = "Money Twins"

以上就是关于sql合并重复的字段全部的内容，包括:sql合并重复的字段、如何使用 devexpress 里 pivotGridControl 控件 从SQL数据库读取image类型显示图片、如何绑定DevExpress PivotGridControl到数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！