如何根据其他列GET和COUNT个列值 [英] how do GET and COUNT one column value base on other column

问题描述

我已经在表单中下拉了，当选择任何值时会触发一个事件。

在数据库中有一个表有两列第一列是链接，每个值下拉一个值这在第二列中有多个值，我如何计算并在表单的文本框中显示这些值。

i have drop down in the form,and an event is fire when any value is selected.
in the database have a table there are two column one is link with drop down against each value of this there are multiple value in the 2nd column, how can i count and display these value in the textboxes of the form.

<div class="form-group">
           <label class="col-sm-2 control-label">
                   Select Sector:
               </label>
               <div class="col-sm-3">
                  <asp:DropDownList CssClass="form-control" runat="server" MaxLength="7" ID="ddlToSector" OnSelectedIndexChanged="ddlToSector_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList>
                    </div>
           <label class="col-sm-2 control-label">
               Strenght:
           </label>
           <div class="col-sm-1">
               <asp:TextBox runat="server" ID="txtStrenght" AutoPostBack="true" ></asp:TextBox>
           </div>
       </div>

       <div class="form-group">
           <label class="col-sm-2 control-label">
               OnLeave:
           </label>
           <div class="col-sm-3">
               <asp:TextBox runat="server" ID="txtOnLeave" AutoPostBack="true" ></asp:TextBox>
           </div>
           <label class="col-sm-2 control-label">
               Absent:
           </label>
           <div class="col-sm-3">
               <asp:TextBox runat="server" ID="txtAbsent" AutoPostBack="true"></asp:TextBox>
           </div>
       </div>

c＃

c#

protected void ddlToSector_SelectedIndexChanged(object sender, EventArgs e)
{
    var sectorStrength = _service.GetAllEmployeeDuty().Where(x => 
        x.ToSector_Id == SafeConvert.ToInt32(ddlToSector.SelectedValue));
    txtStrenght.Text = sectorStrength.ToString();

    var sectorAbsent = _service.GetAllEmployeeAbsent().Where(x => 
        x.Sector_Id ==SafeConvert.ToInt32(ddlToSector.SelectedValue)).Count();
    txtAbsent.Text = sectorAbsent.ToString();

    var empStatus = _service.GetAllEmployeeStatus().Where(x => 
        x.Rank_Id == SafeConvert.ToByte(ddlToSector.SelectedValue)).Count();
    txtSpecialDuty.Text = empStatus.ToString();

第一个sectorStrenght工作正常，它从表中返回确切值

问题在EmployeeStatus表中，我对相同的扇区ID有不同的值，

i希望在选择任何扇区时，查询将在此表中检查员工ID不同的员工状态然后计算并返回到文本框

First sectorStrenght work fine ,it return exact value from the table
problem is in EmployeeStatus table in which i have different value against same Sector Id,
i want that when any sector is selected query will check in this table againt Sector ID DIFFERENT status of employee then count it and return in to text box

Here is EmployeeStatusType table.
USE [CTPHR]
GO

/****** Object:  Table [dbo].[EmployeeStatusType_Id]    Script Date: 11/21/2014 10:03:15 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EmployeeStatusType_Id](
	[Id] [tinyint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](25) NOT NULL,
	[IsDutable] [bit] NOT NULL,
 CONSTRAINT [PK_EmployeeStatusType_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

this is 2nt table against sector id there are different employeestatustype ,this sector id is used to fire an event .
USE [CTPHR]
GO

/****** Object:  Table [dbo].[EmployeeStatus]    Script Date: 11/21/2014 10:06:35 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EmployeeStatus](
	[Id] [tinyint] IDENTITY(1,1) NOT NULL,
	[Employee_Id] [varchar](8) NOT NULL,
	[EmpstatusType_Id] [tinyint] NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL,
	[Sector_Id] [smallint] NOT NULL,
 CONSTRAINT [PK_Mp_status] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[EmployeeStatus]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeStatus_EmployeeStatus] FOREIGN KEY([Id])
REFERENCES [dbo].[EmployeeStatus] ([Id])
GO

ALTER TABLE [dbo].[EmployeeStatus] CHECK CONSTRAINT [FK_EmployeeStatus_EmployeeStatus]
GO

[Id]  [Employee_Id] [EmpstatusType_Id][StartDate][EndDate][Sector_Id]
1           333        1                     xxx   xxx        1
2           ddd        1                     xxx   xxx        1
3           www        2                     xxx   xxx        1
4           fff        4                     xxx   xxx        1
5           ddd        3                     xxx   xxx        1

这是数据库记录的格式。对同一扇区有不同的价值。如何计算EmpstatusType_Id，我如何计算empstatusType并在文本框中显示它们。上面解释

this is the format of database records.there are different value against same sector. how can calculate EmpstatusType_Id ,how can i calculate empstatusType and show them in text box.as explain above

推荐答案

我明白你想要计算不同的EmpstatusType_Id每个部门。



这里你去

What i have understand that you want to count the distinct EmpstatusType_Id against each sector.

Here you go

select 
     count(distinct EmpstatusType_Id ) 
from EmployeeStatus 
where Sector_Id=@SectorId

这篇关于如何根据其他列GET和COUNT个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！