主页 / user-299706

Mikhail's questions

Martin Hope
Mikhail
Asked: 2023-11-17 08:03:12 +0800 CST
  • 5

我有下表作为示例。它包含 5 个请求orderId，每个请求包含一定数量的操作，actionId其中显示请求的进度：

|ordr| data                                                                                      |
| -- | ----------------------------------------------------------------------------------------- |
| 1  | {"actionId":1,"curNum":1,"maxNum":3,"status":"READY","templateId":4,"errMsg":null}        |
| 1  | {"actionId":1,"curNum":1,"maxNum":3,"status":"SUCCESS","templateId":4,"errMsg":null}      |
| 1  | {"actionId":2,"curNum":2,"maxNum":3,"status":"READY","templateId":4,"errMsg":null}        |
| 1  | {"actionId":2,"curNum":2,"maxNum":3,"status":"SUCCESS","templateId":4,"errMsg":null}      |
| 1  | {"actionId":3,"curNum":3,"maxNum":3,"status":"READY","templateId":4,"errMsg":null}        |
| 1  | {"actionId":3,"curNum":3,"maxNum":3,"status":"SUCCESS","templateId":4,"errMsg":null}      |
| 2  | {"actionId":4,"curNum":1,"maxNum":3,"status":"READY","templateId":5,"errMsg":null}        |
| 2  | {"actionId":4,"curNum":1,"maxNum":3,"status":"SUCCESS","templateId":5,"errMsg":null}      |
| 2  | {"actionId":5,"curNum":2,"maxNum":3,"status":"READY","templateId":5,"errMsg":null}        |
| 2  | {"actionId":5,"curNum":2,"maxNum":3,"status":"SUCCESS","templateId":5,"errMsg":null}      |
| 2  | {"actionId":6,"curNum":3,"maxNum":3,"status":"READY","templateId":5,"errMsg":null}        |
| 2  | {"actionId":6,"curNum":3,"maxNum":3,"status":"SUCCESS","templateId":5,"errMsg":null}      |
| 3  | {"actionId":7,"curNum":1,"maxNum":2,"status":"READY","templateId":6,"errMsg":null}        |
| 3  | {"actionId":7,"curNum":1,"maxNum":2,"status":"FAIL","templateId":6,"errMsg":"BAD REQUEST"}|
| 4  | {"actionId":8,"curNum":1,"maxNum":2,"status":"UNKNOWN","templateId":7,"errMsg":null}      |
| 4  | {"actionId":8,"curNum":1,"maxNum":2,"status":"UNKNOWN","templateId":7,"errMsg":null}      |
| 5  | {"actionId":7,"curNum":1,"maxNum":2,"status":"READY","templateId":6,"errMsg":null}        |
| 5  | {"actionId":7,"curNum":1,"maxNum":2,"status":"FAIL","templateId":6,"errMsg":"BAD REQUEST"}|

如何使用 Stream Api 获取如下报告（为了清晰起见，显示在表中）：

| TemplateId | TotalRunOrder  |SuccessRunOrder | FailRunOrder  |FailOrdersInfo  |
| ---------- | -------------- | -------------- | --------------|----------------|
| 4          | 1              | 1              | 0             | null           |
| 5          | 1              | 1              | 0             | null           |
| 6          | 2              | 0              | 2             | [{"OrderId":3,"ErrMsg":"BAD REQUEST"},{"OrderId":5,"ErrMsg":"BAD REQUEST"}]|
| 7          | 0              | 0              | 0             | null           |

报告字段如何计算的说明：

  • TemplateId- 从现场的初始表中获取data
  • TotalRunOrder- 如果其中OrderId至少有一种状态"READY"
  • SuccessRunOrder- 我们在一个中寻找一行OrderId，curNum=maxNum如果状态在其中，"SUCCESS"那么我们认为申请成功
  • FailRunOrder- 如果其中OrderId至少有一种状态，"FAIL"那么我们认为申请失败
  • FailOrdersInfo- 以数组的形式显示信息，其中我们显示哪些OrderId失败并显示ErrMsg错误文本
java
Martin Hope
Mikhail
Asked: 2020-03-26 13:33:00 +0800 CST
  • 2

我维护的服务器有很多 CLR 对象。这是我第一次遇到他们。也许它们不能正常工作，需要优化。没有来源。我怎样才能找到这些对象的代码？它们可能是用 c# 编写的。我试图运行分析器，但除了“EXEC 'Clr 对象的名称'”之外，我没有在那里看到它。据我了解，我们正在谈论反汇编 DLL 文件？

sql-server
Martin Hope
Mikhail
Asked: 2020-09-13 21:18:50 +0800 CST
  • 4

有一个包含 CTE 的请求。表上有必要的索引。请求第一次足够快地完成（8 秒）。此外，我想WHERE通过不在索引中的字段在 CTE（过滤数据）内添加一个部分 => 自然，查询开始工作更长时间。我看到了必要的数据，现在我想回到原来的执行（在 8 秒内），我删除了之前添加的部分WHERE，但是请求现在运行了很长时间（大约 15 分钟）并且不想滚动回到主要状态:-(

应用了什么：

  • 更新了整个数据库的统计信息 - 没有帮助。
  • 查询计划中没有“重”运算符

PS 数据没有插入到表中；无需重建索引。告诉我在哪里进一步挖掘？ 

SET NOCOUNT ON
DECLARE @day TABLE ([from] datetime NOT NULL, [to] datetime NOT NULL)
DECLARE @current datetime = '20190501'
WHILE @current < '20190601'
BEGIN
    INSERT INTO @day SELECT @current, DATEADD(day, 1, @current)
    SET @current = DATEADD(day, 1, @current)
END
SET NOCOUNT OFF
;WITH cte AS(SELECT
Country = 'Russia',
MacroRegion = bsp.Name,
Customer_Name = d.Name,
Contract_Number = d.ExtID,
Terminal_Device_Number = ISNULL((SELECT pc.Value
                    FROM Contact pc
                    JOIN Contact ac ON ac.Demasked_ID = pc.ID
                    WHERE ac.ID = a.Contact_ID),v_apply.Device_ID),
Service_Name  =bst.name,
StartDate=bs.firstinsert,     
EndDate = bs.lastinsert,     
Vehicle_ID=v_apply.VEHICLE_ID,
Billing_Service_ID = bs.Billing_Service_id,
Service_Type_Category=bst.Service_Type_Category,
Asid_ID = a.ID,
RenderedLastTime =(SELECT MAX(t.LastTime)
               FROM(SELECT LastTime = MAX(rsi.RenderDate)
                    FROM RenderedServiceItem rsi
                    WHERE rsi.Billing_Service_ID = bs.Billing_Service_id
                    UNION ALL
                    SELECT LastTime =(SELECT TOP (1) vltl.InsertTime
                                      FROM dbo.VEHICLE_LOG_TIME_LAST vltl 
                                      (NOLOCK)
                                      WHERE vltl.Vehicle_ID = 
                                      v_apply.VEHICLE_ID))t),
RANK() OVER(PARTITION BY  CAST (firstinsert as date),d.name  ORDER BY 
firstinsert) AS Z,
FirstInsert=bs.firstinsert
FROM Asid a 
inner join Department d  on a.Department_ID = d.DEPARTMENT_ID
inner join VEHICLE v on v.DEPARTMENT = d.DEPARTMENT_ID
left  join Billing_Service_Provider bsp  ON bsp.Billing_Service_Provider_ID 
                                            = a.Billing_Service_Provider_ID
outer APPLY (select (select top(1)  hbs.ACTUAL_TIME
                                from H_Billing_Service hbs 
                                where hbs.Asid_ID = a.ID 
                                and hbs.ACTUAL_TIME between d.[from] and d. 
                                [to]
                                order by hbs.ACTUAL_TIME asc)firstinsert,
                (select top(1)  hbs.ACTUAL_TIME
                                from H_Billing_Service hbs 
                                where hbs.Asid_ID = a.ID 
                                and hbs.ACTUAL_TIME between d.[from] and d. 
                                [to]
                                order by hbs.ACTUAL_TIME desc)lastinsert,
                (select top(1)  hbs.Billing_Service_Type_ID
                                from H_Billing_Service hbs
                                where hbs.Asid_ID=a.ID) 
                                Billing_Service_Type_ID,
                (select top(1)  hbs.ID
                                from H_Billing_Service hbs  
                                where hbs.Asid_ID = a.ID )Billing_Service_id
                from @day d         
             )bs
inner join Billing_Service_Type bst ON bst.ID = bs.Billing_Service_Type_ID

 /*Вот в этой секции началась проблема, то есть без неё было быстро, с ней 
 стало медленно. Затем после комментирования сей секции запрос снова быстро 
 отрабатывать не хочет
 and bst.Service_Type_Category in ('Group1', 'Group2', 'Group3','Group4')*/
OUTER APPLY(SELECT v.Vehicle_ID, Device_ID =  CONVERT(varchar(32), 
ci.device_id)
        FROM MLP_Controller mlpc
        right JOIN Controller c ON c.CONTROLLER_ID = mlpc.Controller_ID
        JOIN Vehicle vs ON v.VEHICLE_ID = c.VEHICLE_ID
        LEFT OUTER JOIN CONTROLLER_INFO ci ON ci.CONTROLLER_ID = 
c.CONTROLLER_ID
        WHERE v.VEHICLE_ID = vs.VEHICLE_ID) v_apply
WHERE d.IsCommercial = 1 AND  bs.firstinsert is not null
),cte2 AS
(SELECT 
cte.Country,
cte.MacroRegion,
cte.Customer_Name,
cte.Contract_Number,
cte.Terminal_Device_Number,
cte.Service_Type_Category,
cte.Service_Name,
cte.Firstinsert,
cte.StartDate,
cte.EndDate,
cte.Asid_ID,
cte.Billing_Service_ID,
cte.Z,
RenderedCount = CASE
                  WHEN cte.Service_Type_Category IN ('Group1', 'Group2', 
                  'Group3','Group4')
                  THEN
                     (SELECT [Count] = COUNT(1)
                     FROM @day d
                     WHERE EXISTS
                       (SELECT 1
                        FROM  H_Billing_Service hbs 
                        WHERE hbs.Asid_ID = cte.Asid_ID
                        AND hbs.ACTUAL_TIME BETWEEN d.[from] AND d.[to]
                        AND hbs.ACTUAL_TIME BETWEEN cte.Firstinsert AND 
                        cte.EndDate
                       )
                     )
                  ELSE 0
               END,
 LbsPositionCount =(SELECT COUNT(1)
                    FROM Position_Radius pr (NOLOCK)
                    WHERE pr.Vehicle_ID = cte.Vehicle_ID
                    AND pr.Log_Time BETWEEN DATEDIFF(ss, '1970', 
                    cte.StartDate) AND DATEDIFF(ss, '1970', cte.EndDate) 
                    AND pr.Type = 1 
                    ),
 LbsPositionRequested = (SELECT COUNT(1)
                         FROM Command c
                         WHERE c.Target_ID = cte.Vehicle_ID
                         AND c.Date_Received BETWEEN cte.StartDate AND 
                         cte.EndDate
                         AND c.Type_ID = 20 
                        ),
RenderedLastTime
FROM cte cte
where s.Z=1)
select * from cte2

执行计划： 在此处输入图像描述

sql
Martin Hope
Mikhail
Asked: 2020-01-09 17:53:37 +0800 CST
  • 1

我正在尝试制作电话簿的简化版本，但无法在下拉列表中显示区号枚举名称，因此我的模型如下所示：

using System.ComponentModel.DataAnnotations;
public class User
{
    public string CodeCountry { get; set; }
    public string CodeUser { get; set; }
    public CityCode CodeCity { get; set; }
}
public enum CityCode
{
    [Display(Name = "495")]
    One,
    [Display(Name = "351")]
    Two,
    [Display(Name = "812")]
    Three
}

控制器：

public class HomeController : Controller
{
    public ActionResult Index()
    {
        User user = new User();
        user.CodeCountry = "+7";
        user.CodeUser = "123456789";
        user.CodeCity = CityCode.Two;
        return View(user);
    }

}

看法：

@model MVC_Fund6_1.Models.User
@{
ViewBag.Title = "Home Page";
}
<h2>EditorFor</h2>

@Html.LabelFor(x => x.CodeCountry)<br />
@Html.EditorFor(x => x.CodeCountry) <br />

@Html.LabelFor(x => x.CodeCity)<br />
@Html.EditorFor(x => x.CodeCity,"UserTemplate") <br />

@Html.LabelFor(x => x.CodeUser)<br />
@Html.EditorFor(x => x.CodeUser)

UserTemplate 的自定义视图：

@using MVC_Fund6_1.Models
@model CityCode
<select id="CityCode" name="CityCode">
@foreach (CityCode value in Enum.GetValues(typeof(CityCode)))
{

    <option value="@value">
        @value
    </option>
}
</select>

结果，在城市代码的下拉列表中，我无法准确地得到名字，即不是“一二三”，而是“495,351,812”：

在此处输入图像描述

怎么样，告诉我？试图在模型中装饰DataAnnotations，但是没有用。我尝试为模型中的枚举器分配名称，并通过 Enum.GetNames 将其显示在专门的模板中，但它也不起作用

c#
Martin Hope
Mikhail
Asked: 2020-11-19 21:34:31 +0800 CST
  • 0

我有两个表部门和用户。它们通过 DepId 字段连接： 在此处输入图像描述

Departments 表中预先填充了数据：

在此处输入图像描述

我需要制作一个 Web 表单，我可以在其中对我的用户表执行 CRUD 操作（我匆忙将它画出来）：

在此处输入图像描述

现在的问题是如何编写一个存储过程，该存储过程将根据可用信息 NameDep（这些是我的部门名称）填充用户表（DepId 字段）。级联升级是值得的。 

CREATE PROC hp
(
@FName varchar(20), @LName varchar(20), @NameDep nvarchar(20),@Event 
varchar(20),@DepId int
)
AS 
  BEGIN
  IF(@Event='Select')
  BEGIN
  SELECT * FROM Users ORDER BY FirstName ASC;
END

ELSE IF(@Event='Add')
BEGIN
  IF @NameDep = 'Дирекция' SET @DepId=1;
  INSERT INTO Users (FName,LName,DepId) VALUES(@FName,@LName,@DepId);
  IF @NameDep = 'Администрация' SET @DepId=2;
  INSERT INTO Users (FName,LName,DepId) VALUES(@FName,@LName,@DepId);
  IF @NameDep = 'Эксплуатация' SET @DepId=3;
  INSERT INTO Users (FName,LName,DepId) VALUES(@FName,@LName,@DepId);
END
END

如果这是正确的，那么我将按照相同的原则进行更新并删除

c#
Martin Hope
Mikhail
Asked: 2020-11-09 17:01:50 +0800 CST
  • 2

我有 2 个表，我想为每个表添加一行并将更改传输到数据库。我同时使用SqlCommandBuilder。这是我的代码：

 string connectionString = @"Data Source=WKS456\SQLEXPRESS;Initial Catalog=ShopDB;Integrated Security=True";
        string sql = "SELECT * FROM Customers;Select * FROM Employees";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable customers = ds.Tables[0];
            DataTable employees = ds.Tables[1];

            // добавим новую строку в первую таблицу:

            DataRow newRow = customers.NewRow();
            newRow["FName"] = "x";
            newRow["LName"] = "x";
            newRow["Address1"] = "x";
            newRow["City"] = "x";
            newRow["Phone"] = "x";
            customers.Rows.Add(newRow);

            // добавим новую строку во вторую таблицу:

            DataRow newRow1 = employees.NewRow();
            newRow1["EmployeeID"] = 101;
            newRow1["FName"] = "x";
            newRow1["LName"] = "x";
            newRow1["MName"] = "x";
            newRow1["Salary"] = 111;
            newRow1["PriorSalary"] = 111;
            newRow1["HireDate"] = "9999 / 12 / 31";
            newRow1["ManagerEmpID"] = 5;
            employees.Rows.Add(newRow1);

            //Создаю SqlCommandBuilder:

        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
        adapter.Update(ds);

此外，我观察到一项奇怪的工作，即：更改只发送到一个表，并发送到第一个表（客户）。在第二个（员工） - 沉默。我试图注释掉在第一个表中添加一行，即 只剩下第二个 - 一切都解决了 - 在第二个表（员工）中 - 出现了。但是为什么它不能同时在两张桌子上工作呢？我尝试了不同的 adapter.Update(...) 重载 - 但似乎没有任何帮助。

c#
Martin Hope
Mikhail
Asked: 2020-10-30 20:05:00 +0800 CST
  • 3

我的数据集中有 2 个表：客户，按“客户编号”列连接的订单：

在此处输入图像描述 在此处输入图像描述

我的任务是按城市显示销售统计数据。这是我的代码：

var query = from c in customers.AsEnumerable()
                    let countSale = orders.Count(o => o.CustomerNo == c.CustomerNo)
                    select new
                    {
                        c.City,
                        countSale
                    };
        dataGridView1.DataSource = query.ToList();

这是我的输出：

在此处输入图像描述

我不喜欢这个输出，如何在 LINQ 中执行子查询来打印城市及其销售额（对于我的 countSale 变量）？简单地说，最后我想看到类似的东西：

在此处输入图像描述

sql-server
Martin Hope
Mikhail
Asked: 2020-10-22 19:54:01 +0800 CST
  • 1

我在强类型 ShopDB 数据集中有 2 个表。Orders 表通过外键 FK_Employees_Orders 通过 EmployeeID 字段连接到Employees 表：

在此处输入图像描述

我的任务是显示每个员工的总销售额。（做类似 LEFT JOIN 的事情，但我想以不同的方式解决问题）。这是我的代码：

ShopDB shopDB = new ShopDB();
var employeesTableAdapter = new ShopDBTableAdapters.EmployeesTableAdapter();
employeesTableAdapter.Fill(shopDB.Employees);

var ordersTableAdapter = new ShopDBTableAdapters.OrdersTableAdapter();
ordersTableAdapter.Fill(shopDB.Orders);

ShopDB.EmployeesDataTable employees = employeesTableAdapter.GetData();
ShopDB.OrdersDataTable orders = ordersTableAdapter.GetData();
employees.Columns.Add("AggregateColumn", typeof(double), "Count(Child(FK_Orders_Employees).EmployeeID)"); /*название отношения взял из определения ShopDB.Designer.cs*/

 foreach (DataRow employeeRow in employees.Rows)
        {
            foreach (DataColumn employeeColumn in employees.Columns)
                Console.WriteLine("{0}: {1}", employeeColumn.ColumnName, employeeRow[employeeColumn]);

调试时，在添加列的那一行出现异常：错误

System.Data.EvaluateException HResult=0x80131920 消息 = 复合表达式“System.Data.AggregateNode”中的未绑定引用。Source = System.Data 堆栈跟踪：在 System.Data.AggregateNode.Eval（DataRow 行，DataRowVersion 版本）在 System.Data.DataExpression.Evaluate（DataRow 行，DataRowVersion 版本）在 System.Data.DataTable.EvaluateExpressions（DataColumn 列）到 System.Data.DataColumn.set_Expression(String value) 到 System.Data.DataColumnCollection.AddAt(Int32 index, DataColumn column) 到 System.Data.DataColumnCollection.Add(String columnName, Type type, String expression)

你能建议什么可能是解决方案吗？

c#
Martin Hope
Mikhail
Asked: 2020-09-13 22:35:06 +0800 CST
  • 0

我有一个类Computer，它有一个长度为 5 个元素的数组computers，以及这个类的构造函数、getter 和 setter：

public class Computer {
    private int[] computers = new int [5];

    public Computer(int[] computers) {
        this.computers = computers;
    }

    public int[] getComputers() {
        return computers;
    }

    public void setComputers(int[] computers) {
        this.computers = computers;
    }
}

现在如何在另一个类（Main）中创建该类的 5 个实例，初始化它们并将它们输出到System.out？提前感谢您的回复。

主要课程：

public class Main {

    public static void main(String[] args) {
        int[] x = {1,2,3,4,5};
        Computer computers1 = new Computer(x);
        computers1.setComputers(x);
        computers2.setComputers(x);
        computers3.setComputers(x);
        computers4.setComputers(x);
        computers5.setComputers(x);
        System.out.println(computers1.getComputers());
    }
}

...这里显示了一些废话。我觉得我做错了什么。帮助我理解

java